1

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 = "person@email.com"
        MItem.CC = "OtherPeople@email.com"

    End If
End If
End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
HalfWit
  • 31
  • 6
  • 4
    You have a major issue with lack of attention to detail (which is very important in coding.) There is no item called `oMailItem`. Also you have a `With` paired with an `End If`. Also, you don't assign values to variables like `NewSub.Value =...`. Also no variable `NewSubject`. This could all have been easier for you to notice by adding the line `Option Explicit` at the top of [every] module [forever], as it will "force" you to properly declare and refer to variables, objects, properties, etc... Properly indenting and spacing your code also helped identify these issues. – ashleedawg Jun 04 '18 at 23:10
  • Oh wow... I guess I have been staring at code too long! lol Thanks for pointing that out. I am new to VBA and have been doing a lot of copy paste and troubleshooting to learn. What I don't understand is I have oMailItem in the prior working example for attaching the file and it worked fine... So I need it to be an object, not a variable. I totally missed that. Option Explicit! Got it thanks! That will help a ton!!! – HalfWit Jun 04 '18 at 23:23
  • You're welcome, everyone has to start somewhere, and there is a definite learning curve before it levels out. Did you figure it out? Whichever working example you're referring to, you need to be careful to copy the DIM declarations align with any commands that refer to a variable or object. With `Option Explicit` as the first line, press F9 after *every* change to recompile and check for declaration problems. – ashleedawg Jun 04 '18 at 23:30
  • Well I corrected the first part that was already working and found a few other errors. F9? All that is doing is toggling a break point on and off. I've been using F5... But maybe that does something else... Anyway, I'm still working on it. – HalfWit Jun 04 '18 at 23:36
  • Sorry not F9. Debug -> Compile (or `F5` works too since it compiles before running). I remap Compile to `F9` on my keyboard. If you're still having issues after those corrections (and not getting anymore compile issues) the you can edit [edit] to update your questio's code and description, or else ask a new question. – ashleedawg Jun 05 '18 at 00:41
  • Updated with my working code, however I have a new issue where it isn't allowing me to add another Sub after the End Sub. Not sure why. It let me do three in a row and then suddenly says I can't write code but only comment... – HalfWit Jun 05 '18 at 20:58
  • Nevermind... Not sure why but it started working again... Gremlins... Now I'm working on adding my default signature to the email. – HalfWit Jun 05 '18 at 21:00
  • And got that signature added. I'm getting the hang of this... soon I'll be writing my own code instead of reading pieces of other peoples code and trying to make it work! lol It's tough when your coding experience goes back to the apple IIE, then quick basic, then C, then a little C++, HTML, CSS, JavaScript... And now I'm crosstraining to VBA... It never ends! Thanks for the help! – HalfWit Jun 05 '18 at 21:42

1 Answers1

0
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 = "person@email.com"
        MItem.CC = "OtherPeople@email.com"

    End If
End If
End Sub
HalfWit
  • 31
  • 6