VBA to send email and letting the user select attachments

SaveMeObiWan · Jan 5th 2023

i created an userform to automate some emails i need to send everyday, basically i enter the account number and click a lookup button, this brings up the account and contact info from a database i have in excel. then i enter the doc number and amount and click a send button. a file selection window opens and asks me to select the attachment. after selecting an email is created using the info on the user form and the file is attached.

this works ok when i am selecting one file but sometimes i need to select several attachments. i am having trouble with this part, the code i am using lets me select the files but it doesnt actually attach them.

any advice on how i can set it up for multiple attachments? or is there another route i should be taking?

i was thinking that of maybe having the macro write the file names somewhere on the excel file and then using .attachments.add and pointing at whereever excel wrote those files down. but im not sure how to go about that.

anyone have any ideas?

Code

>'open dialog box to let user choose attachment file
attachFile = Application _
.GetOpenFilename("Files (*.**), *.**", MultiSelect:=True)

Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj    
.Display    
.To = ""    
.CC = ""    
.Subject = ""    
'use .HTMLBody to enable signature    
.HTMLBody = strbody & .HTMLBody
.Attachments.Add attachFile
End With

Display More

**Carim** · Jan 6th 2023

Hello,

You can test following proposal written by Norie

Code

Sub Email_Report()
' Code written by NORIE
Dim OutApp, OutMail As Object
Dim wbFullName As Variant
Dim I As Long

    If MsgBox("Please Select Attachments", vbYesNo) = vbNo Then Exit Sub
    wbFullName = Application.GetOpenFilename("All Files (*.*), *.*", MultiSelect:=True)
    If Not IsArray(wbFullName) Then
        If wbFullName = False Then
            Exit Sub
        End If
    End If
    On Error Resume Next
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = ""
        .Subject = "AddYourSubject"
        If IsArray(wbFullName) Then
            For I = LBound(wbFullName) To UBound(wbFullName)
                .attachments.Add wbFullName(I)
            Next I
        Else
            .attachments.Add (wbFullName)
        End If
        .Display  'Or use .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Display More

Hope this will help

jonny · Jan 9th 2023

To attach multiple files using the code you provided, you will need to iterate over the array of file names returned by the GetOpenFilename function. Here's an example of how you can do this:

Code

attachFile = Application.GetOpenFilename("Files (*.**), *.**", MultiSelect:=True)

If Not IsArray(attachFile) Then
    ' single file was selected
    Set xOutlookObj = CreateObject("Outlook.Application")
    Set xEmailObj = xOutlookObj.CreateItem(0)
    With xEmailObj    
        .Display    
        .To = ""    
        .CC = ""    
        .Subject = ""    
        'use .HTMLBody to enable signature    
        .HTMLBody = strbody & .HTMLBody
        .Attachments.Add attachFile
    End With
Else
    ' multiple files were selected
    For i = LBound(attachFile) To UBound(attachFile)
        ' attach each file
        Set xOutlookObj = CreateObject("Outlook.Application")
        Set xEmailObj = xOutlookObj.CreateItem(0)
        With xEmailObj    
            .Display    
            .To = ""    
            .CC = ""    
            .Subject = ""    
            'use .HTMLBody to enable signature    
            .HTMLBody = strbody & .HTMLBody
            .Attachments.Add attachFile(i)
        End With
    Next i
End If

Display More

I hope this helps! Let me know if you have any questions.

**Carim** · Jan 9th 2023

Hopefully OP will provide his feedback

VBA to send email and letting the user select attachments

Participate now!

Share

Similar Threads

Command Button sends Email to List - with extra flag

Vba to email multiple attachments but do nothing if no attachments AND use SendUsingAccount

Email Individual or Selected Individuals Using VBA/Macro Actions