So far I have been able to attach the file programmatically with this:
Private Sub AttachmentFile()
Dim oLook As Object
Dim oMail As Object
Dim FD As Object
Dim vrtSelectedItem As Variant
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
Set FD = Excel.Application.FileDialog(3)
With oMail
FD.AllowMultiSelect = True
FD.Filters.Clear
FD.Filters.Add "All Files", "*.*"
FD.InitialFileName = "\\ad\dfs\Shared Data\"
If FD.Show = True Then
For Each vrtSelectedItem In FD.SelectedItems
.Attachments.Add vrtSelectedItem
Next
End If
.Display
End With
Set FD = Nothing
Set oMail = Nothing
Set oLook = Nothing
End Sub
Now that the email is created and I can select the file from a specified folder, I am trying to have the change the mail item's subject to the name of the attached file, also replacing the _ (underscore) with a | (pipe symbol).
Here is the now Working code I have. I also added a signature to the email with correct fonts and image of my default signature in outlook.
Private Sub SubNBodyNTo()
Dim myInspector As Outlook.Inspector
Dim MItem As MailItem
Dim myAttachments As Outlook.Attachments
Set myInspector = Application.ActiveInspector
If Not TypeName(myInspector) = "Nothing" Then
If TypeName(myInspector.CurrentItem) = "MailItem" Then
Set MItem = myInspector.CurrentItem
Set myAttachments = MItem.Attachments
MItem.Subject = myAttachments.Item(1).DisplayName
MItem.Subject = Replace(MItem.Subject, "_", " | ")
MItem..HTMLBody = MItem.Subject & " Is attached for your approval." & "<br>" & MItem.HTMLBody
MItem.To = "[email protected]"
MItem.CC = "[email protected]"
End If
End If
End Sub
