Solved

Access - send email with multiple attachments

Posted on 2013-01-23
18
1,903 Views
Last Modified: 2013-01-25
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?
0
Comment
Question by:correlate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +3
18 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 225 total points
ID: 38809731
Are you attachment files always named "Example1.pdf", "Example2.pdf", etc etc? If not, then how do you determine which files to attach to a specific email?

I've never tried to add multiple attachments in a single string. Instead, it would seem that you'd be better off doing this:

With objOutlookMsg
            .To = ""
            .Subject = "Test"
            .Body = "Hi "
            .Attachments.Add  "Path to Attachment1"
            .Attachments.Add "Path to Attaachment2"
            etc etc
            .Save
            .Display

End With
0
 

Author Comment

by:correlate
ID: 38809798
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
0
 
LVL 58
ID: 38809807
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 225 total points
ID: 38809851
Check for a blank string before adding:

If Nz(AttachmentFiles1, "") <> "" Then
  '/ add attachment
End If

If Nz(AttachmentFiles2, "") <> "" Then
  '/ add second attachment
End If

If Nz(AttachmentFiles3,  "") <> "" Then
  '/ add 3rd attachment
End If
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 225 total points
ID: 38809868
How about
            .To = ""
            .Subject = "Test"
            .Body = "Hi "
            If AttachmentFiles1<>"" Then .Attachments.Add AttachmentFiles1
            If AttachmentFiles2<>"" Then .Attachments.Add AttachmentFiles2
            If AttachmentFiles3<>"" Then .Attachments.Add AttachmentFiles3
            .Save
            .Display

Open in new window

0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 50 total points
ID: 38809919
try this one


dim pdfFile as string, pdfPath as string
pdfPath="C:\Users\Public\Documents\"
      With objOutlookMsg
            .To = ""
            .Subject = "Test"
            .Body = "Hi "
			
			pdfFile=dir(pdfPath & "*.pdf")
			while pdfFile<>""
			  
            .Attachments.Add pdfPath & pdfFile
			
			 pdfFile=dir
			wend
            .Save
            .Display

        End With

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38809948
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

0
 

Author Comment

by:correlate
ID: 38810398
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38810467
<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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38810493
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.
0
 

Author Comment

by:correlate
ID: 38810520
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.
0
 

Author Comment

by:correlate
ID: 38810542
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
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 225 total points
ID: 38810550
Or you can use the Split function:
    Dim FileArray As String
    Dim strPath As String
    Dim AttachmentFiles, aFile
    
    FileArray = "Example1.pdf, Example2.pdf,"
    strPath = "C:\Users\Public\Documents\"
    
    AttachmentFiles = Split(FileArray, ",")

    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

0
 
LVL 85
ID: 38810574
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?
0
 

Author Comment

by:correlate
ID: 38810656
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
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 38810694
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38816728
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.
0
 

Author Closing Comment

by:correlate
ID: 38817905
Thank you very much for your help on this one - works great
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question