- Joined
- Jun 15, 2012
- Messages
- 1
- Reaction score
- 0
First time trying to code in Outlook.
I have created a custom Message form with 3 User Defined Fields on the Compose Page and a Command Button on the Read Page. Command Button Code works great and operates as desired. My issue is this. I am using this custom form as an OFT file and calling it from Excel. I would like to fill the 3 UDF's with data from Excel. I have included my code from Excel. I was hoping it would be as easy as accessing the To: and Subject: fields but I see it is not. Could someone help me with the programming for this task. Thanks in advance.
Function NewNameMail()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim strNewNam As String, strOldNam As String
strNewNam = InputBox("Enter Inventory Files New Name." & Chr(13) & "Avoid using '/' and '\' and '-' Characters." & Chr(13) & "Use the '_' character instead.", _
"Rename the Inventory File", _
"Enter the Files New Name Here! ")
cFile = ActiveWorkbook.Name
xx = InStr(5, cFile, "_")
vPre = Left(cFile, xx)
filename = strNewNam
UserName = Environ("USERNAME")
Dim bStarted As Boolean
'Dim oOutlookApp As Outlook.Application
Dim oOutlookApp As Object
'Dim oItem As Outlook.MailItem
Dim oItem As Object
'If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
' ActiveDocument.Save 'so save it
'End If
'see if Outlook is running and if so turn your attention there
'Set oOutlookApp = GetObject(, "Outlook.Application")
Set oOutlookApp = CreateObject("Outlook.Application")
If Err <> 0 Then 'Outlook is not running
'So fire it up
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
'Open a new e-mail message
'Create an Outlook Mail Item
'Set olEmail = oOutlookApp.CreateItem(olMailItem)
'Set olEmail = oOutlookApp.CreateItem(0)
Set olEmail = oOutlookApp.CreateItemFromTemplate("\\wmstutwbdv01\inetpub$\wwwroot\WUW\RIMProgramWorkbase\WorkingFiles\RenameInventory.oft")
With olEmail
.To = "(e-mail address removed)"
.Subject = "Request to Rename Inventory File"
'.tfNewNam = strNewNam
.HTMLBody = UserName & "Would like to Re-Name " & cFile & " to " & vPre & filename & ".xls" & ". <br/><br/>" & _
"If you approve Please Rename the Inventory to the New Name listed and send " & UserName & " an email when the process is complete."
.Controls("tfOldNam") = cFile
.Controls("tfNewNam") = filename
.Controls("tfUserNam") = UserName
The above is of course not working. I want to populate the Custom Fields on the Custom Message form RenameInventory.oft with the values from the varialbes here in Excel. Can this be done???
' .Send
.Display
End With
'If bStarted Then 'If the macro started Outlook, stop it again.
' oOutlookApp.Quit
'End If
'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
End Function
Any help would be greatly appreciated!!!
I have created a custom Message form with 3 User Defined Fields on the Compose Page and a Command Button on the Read Page. Command Button Code works great and operates as desired. My issue is this. I am using this custom form as an OFT file and calling it from Excel. I would like to fill the 3 UDF's with data from Excel. I have included my code from Excel. I was hoping it would be as easy as accessing the To: and Subject: fields but I see it is not. Could someone help me with the programming for this task. Thanks in advance.
Function NewNameMail()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim strNewNam As String, strOldNam As String
strNewNam = InputBox("Enter Inventory Files New Name." & Chr(13) & "Avoid using '/' and '\' and '-' Characters." & Chr(13) & "Use the '_' character instead.", _
"Rename the Inventory File", _
"Enter the Files New Name Here! ")
cFile = ActiveWorkbook.Name
xx = InStr(5, cFile, "_")
vPre = Left(cFile, xx)
filename = strNewNam
UserName = Environ("USERNAME")
Dim bStarted As Boolean
'Dim oOutlookApp As Outlook.Application
Dim oOutlookApp As Object
'Dim oItem As Outlook.MailItem
Dim oItem As Object
'If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
' ActiveDocument.Save 'so save it
'End If
'see if Outlook is running and if so turn your attention there
'Set oOutlookApp = GetObject(, "Outlook.Application")
Set oOutlookApp = CreateObject("Outlook.Application")
If Err <> 0 Then 'Outlook is not running
'So fire it up
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
'Open a new e-mail message
'Create an Outlook Mail Item
'Set olEmail = oOutlookApp.CreateItem(olMailItem)
'Set olEmail = oOutlookApp.CreateItem(0)
Set olEmail = oOutlookApp.CreateItemFromTemplate("\\wmstutwbdv01\inetpub$\wwwroot\WUW\RIMProgramWorkbase\WorkingFiles\RenameInventory.oft")
With olEmail
.To = "(e-mail address removed)"
.Subject = "Request to Rename Inventory File"
'.tfNewNam = strNewNam
.HTMLBody = UserName & "Would like to Re-Name " & cFile & " to " & vPre & filename & ".xls" & ". <br/><br/>" & _
"If you approve Please Rename the Inventory to the New Name listed and send " & UserName & " an email when the process is complete."
.Controls("tfOldNam") = cFile
.Controls("tfNewNam") = filename
.Controls("tfUserNam") = UserName
The above is of course not working. I want to populate the Custom Fields on the Custom Message form RenameInventory.oft with the values from the varialbes here in Excel. Can this be done???
' .Send
.Display
End With
'If bStarted Then 'If the macro started Outlook, stop it again.
' oOutlookApp.Quit
'End If
'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
End Function
Any help would be greatly appreciated!!!