Tom Crowfoot
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:
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)
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?
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
AttachmentFiles = AttachmentFiles & "C:\Users\Public\Documents
it needs to be:
AttachmentFiles = AttachmentFiles & "C:\Users\Public\Documents
and then after your done building the string, strip off the last semi-colon:
AttachmentFiles = Left$(AttachmentFiles, Len(AttachmentFiles)-1)
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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:
Any ideas anyone?
Many thanks
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
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.
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.
ASKER
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.
Also looking at your code it takes all the pdfs from that filepath rather than selected ones.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
Hi LSMConsulting
Just spotted it (I had AttachmentFiles2 = "" below rather than AttachmentFiles3 = "")
Apologies for that
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 = ""
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.
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.
ASKER
Thank you very much for your help on this one - works great
ASKER
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
Open in new window
Any ideas