Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access - send email with multiple attachments

Posted on 2013-01-23
18
Medium Priority
?
2,706 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 900 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 900 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 900 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 200 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 900 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

926 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