Link to home
Start Free TrialLog in
Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access - send email with multiple attachments

Dear Experts

I am looking to be able to send an email from Access with multiple attachments (these are previously saved files outside of the Db).  The problem I face is that the number of attachments will vary, but its never more than 5

So what I was looking to do was build a string with the full file path of each document (call it "AttachmentFiles") and use the following code to send it:

Dim objOutlook As Object
Dim objOutlookMsg As Object

 

        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(0)
   

        With objOutlookMsg
            .To = ""
            .Subject = "Test"
            .Body = "Hi "
            .Attachments.Add AttachmentFiles
            .Save
            .Display

        End With
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing

Open in new window


To build the AttachmentFiles string I was looking to use something like this - but I don't know what would be an appropriate seperator (ie a ; as you'd have between email addresses)

Dim AttachmentFiles as string
AttachmentFiles = ""

If Not IsMissing("C:\Users\Public\Documents\Example1.pdf") Then
AttachmentFiles = "C:\Users\Public\Documents\Example1.pdf" **** + a seperator? '****
End If

If Not IsMissing("C:\Users\Public\Documents\Example2.pdf") Then
AttachmentFiles = AttachmentFiles &  "C:\Users\Public\Documents\Example2.pdf" '**** + a seperator? '****
End If

If Not IsMissing("C:\Users\Public\Documents\Example3.pdf") Then
AttachmentFiles = AttachmentFiles &  "C:\Users\Public\Documents\Example3.pdf"
End If

' etc etc 

Open in new window


Is this the right way to do it & if so how do I seperate the attachments?

or

Anyone know a different method to achieve this?
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tom Crowfoot

ASKER

Hi LSMConsulting,

Thanks for that, works all fine unless there is a blank string (ie I have only 2 of the 3 documents I wish to attach) if I can crack that bit then we're away

The code I have is

Dim objOutlook As Object
Dim objOutlookMsg As Object


Dim AttachmentFiles1 As String
Dim AttachmentFiles2 As String
Dim AttachmentFiles3 As String

AttachmentFiles1 = "C:\Users\Public\Documents\Example1.pdf"
AttachmentFiles2 = "C:\Users\Public\Documents\Example2.pdf"
AttachmentFiles3 = ""


        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(0)
   

        With objOutlookMsg
            .To = ""
            .Subject = "Test"
            .Body = "Hi "
            .Attachments.Add AttachmentFiles1
            .Attachments.Add AttachmentFiles2
            .Attachments.Add AttachmentFiles3
            .Save
            .Display

        End With
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing

Open in new window


Any ideas
Instead of:

AttachmentFiles = AttachmentFiles &  "C:\Users\Public\Documents\Example3.pdf"

it needs to be:

AttachmentFiles = AttachmentFiles &  "C:\Users\Public\Documents\Example3.pdf;"


and then after your done building the string, strip off the last semi-colon:

 AttachmentFiles = Left$(AttachmentFiles, Len(AttachmentFiles)-1)

Jim.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here's another way:
    Dim AttachmentFiles, aFile
    Dim strPath As String
        
    AttachmentFiles = Array("Example1.pdf", "Example2.pdf", "Example3.pdf", "Example4.pdf", "Example5.pdf")
    strPath = "C:\Users\Public\Documents\"
    
    With objOutlookMsg
        .TO = ""
        .SUBJECT = "Test"
        .BODY = "Hi "
        For Each aFile In AttachmentFiles
           .Attachments.add strPath & aFile
        Next
        .Save
        .Display
    End With

Open in new window

Thanks for these - have been going through each ...

IrogSinta (1st Method) & LSMConsulting - These work all fine until I have blank string, so if there is no "Example3.pdf", then the code just adds the 1st attachment rather than the 1st & 2nd.

capricorn1 - unfortunately, & I should have mentioned it the attachments will not all be pdfs & also will be filed alongside other documents which I dont want to send.

IrogSinta (2nd Method) - this looks to have the potential to be able to create a string to populate the array (ie I can specify which docs in the string), but I cant seem to get the syntax right ... what I'm trying is this:
Dim objOutlook As Object
Dim objOutlookMsg As Object

Dim FileArray As String
FileArray = "'Example1.pdf', 'Example2.pdf',"

 Dim AttachmentFiles, aFile
    Dim strPath As String
        
    AttachmentFiles = Array(FileArray)
    strPath = "C:\Users\Public\Documents\"

        
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(0)
   


With objOutlookMsg
        .TO = ""
        .SUBJECT = "Test"
        .BODY = "Hi "
        For Each aFile In AttachmentFiles
           .Attachments.Add strPath & aFile
        Next
        .Save
        .Display
    End With
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing

Open in new window


Any ideas anyone?

Many thanks
<capricorn1 - unfortunately, & I should have mentioned it the attachments will not all be pdfs & also will be filed alongside other documents which I dont want to send.>

i don't understand, what you meant.

in your codes, you are giving  .pdf examples

what other files will be attached ?

the codes i posted will only grab the .pdf files from the folder, so as the other comments.
Unfortunately when using the Array function, you cannot use a string variable for the argument list.  You have to do it the way I showed you.
The files that maybe attached are a mixture of pdfs, word docs and jpegs - I should have mentioned this at the outset - apologies for that.  

Also looking at your code it takes all the pdfs from that filepath rather than selected ones.
Hi IrogSinta

Thanks for that, thats a shame, I managed to get it to do just one pdf using FileArray = "Example1.pdf" and so assumed I could build it out from there
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
then the code just adds the 1st attachment rather than the 1st & 2nd.
I don't see how that possible, using the syntax I suggested. The code would attempt to add any of the 3 Attachments, if the value in the respective Attachment variable is not null. Are you certain you have the syntax correct?
Hi LSMConsulting

Just spotted it (I had AttachmentFiles2 = "" below rather than AttachmentFiles3 = "")

AttachmentFiles1 = "C:\Users\Public\Documents\Example1.pdf"
AttachmentFiles2 = "C:\Users\Public\Documents\Example2.pdf"
AttachmentFiles2 = ""

Open in new window


Apologies for that
This is a reference to a commercial email addin product in case you're interested or someone who's looking at this doesn't want to do any programming.

Our Total Access Emailer program, http://www.fmsinc.com/MicrosoftAccess/Email.asp does what you're seeking without havingt to program anything,

It will automatically send personalized, individual emails to each recipient using field values in the message and attaching existing files, or filtered reports/data for each person. Multiple reports can be attached as PDF files. You can include files from disk by specifying the same ones for everyone or listing them in a field for each record for each person to receive a different set of files. They can also be zipped and password protected if you need extra security. It uses SMTP to bypass Outlook and the MAPI security limitations.

A free trial is available: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

The Professional Version includes a VBA interface and royalty-free runtime library if you want to automate and share it with others. Hope this helps.
Just want to make sure you saw my earlier post here because we posted about the same time and you may have inadvertently missed it.
Thank you very much for your help on this one - works great