Solved

Access - send email with multiple attachments

Posted on 2013-01-23
18
1,061 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
  • 6
  • 5
  • 3
  • +3
18 Comments
 
LVL 84

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 57
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
 
LVL 84

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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 84
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:LukeChung-FMS
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now