Send Email & select attachment from insert file list

mnmhenry · May 9, 2022

Hi Peeps,

I'm stuck again and need the brains-trust!

I am sending an email from Excel. At the moment I am pasting a file address and name into a cell and completing the email. I would like VBA to be able to call the "Insert File" pop up box so I can use the directory to select the file to attach. Is this possible?

O4 & O5 are the cells holding the attachments.

My Code so far -

Sub Sent_Test_Email()
On Error GoTo ErrHandler

' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail

.to = ActiveCell.Offset(, 6).Value
.CC = ActiveCell.Offset(, 7).Value
.Subject = ActiveCell.Offset(, 8).Value
.Body = ActiveCell.Offset(, 9).Value
.Attachments.Add Range("O4").Value
.Attachments.Add Range("O5").Value
'.HTMLBody = strbody & " " & Signature
'.HTMLBody = "<HTML><BODY>Font Color the bold text here. New line with underline Font size</BODY></HTML>"
.Display ' Display the message in Outlook.
End With

' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing

ErrHandler:
'

ActiveCell.Offset(, 1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'ActiveCell.Offset(0, 1).Select
' Down - Across

End Sub

Thanks for any help.

John_w · May 10, 2022

Add this immediately after the Sub line:

VBA Code:

    Dim attachmentFullName As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select file to attach to email"
        .AllowMultiSelect = False
        If Not .Show Then Exit Sub
        attachmentFullName = .SelectedItems(1)
    End With

and replace one of the .Attachments.Add with:

VBA Code:

    .Attachments.Add attachmentFullName

Send Email & select attachment from insert file list

mnmhenry

Board Regular

Excel Facts

John_w

MrExcel MVP

Similar threads

Forum statistics

Share this page

Send Email & select attachment from insert file list

mnmhenry

Board Regular

Excel Facts

John_w

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock