Excel365 - mail file (VBA)

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Excel365 - mail file (VBA)

Post by Lost Paul »

Hi,
I'm trying to set up a file that will attach itself to an e-mail, with recipients, subject and body already populated.
N.B. Our system requires users to assign a confidentiality before sending, which cannot be automated.

So the idea is that the user will click a button, the e-mail will appear ready for; review, confidentiality assigning then sending.

Did find previous post, but nothing happens (below).

Code: Select all

Sub Mail_workbook_Outlook()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long

    ' Save workbook
    ActiveWorkbook.Save
    Set wsh = Worksheets("Recipients")
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        m = wsh.Range("B2").End(xlDown).Row
        For r = 2 To m
            .Recipients.Add wsh.Range("A" & r).Value
        Next r
        .Subject = "TESTER" & Format(Now(), "dd-mm-yy")
        .Body = "Hi," & vbCr & vbLf & "Please see the attached Dock Fail"
        .Attachments.Add ActiveWorkbook.FullName
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel365 - mail file (VBA)

Post by HansV »

1) In my experience, code like this works best if Outlook is already running. If not, messages might remain in the Outbox.
2) Change .Send to .Display to make Outlook display the message instead of sending it without interaction.
3) If the recipients are in column A, change the line

Code: Select all

        m = wsh.Range("B2").End(xlDown).Row
to

Code: Select all

        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Excel365 - mail file (VBA)

Post by Lost Paul »

Thank you - that's done it