Can you attach multiple photos in Access and generate an email?

[anoble1]

Hi,

I am writing a database that I want to be able to hit a button and a email generates and pulls multiple photos in a specified folder on the network and throw them in a email for me to send. The photos are .jpg

Is there a way to have multiple picture attachments? I think DoCmd.OutputTo acOutputReport only does 1 record according to a post I was reading on here earlier today. So, just wanting them all attached to the email.

Any help?
Thanks,
-Andy

[twinnyfo]

There is another method for sending e-mails, I'm trying to dig up an example to show you....

[anoble1]

That would be great!

[zmbd]

I know the basic command is fairly limited.
Using automation you can do alot more:

http://support.microsoft.com/kb/161088

Here's the trick... I haven't used this to send more than one attachment so you'll have to look at the code a tad.

So if we use a record set to pull the attachments then the code might be changed to something like:

'do the open record set stuff and check to make
'sure there are attachments then something like
'modify the MS Code to read the record set
'
Do While Not zrs.EOF
AttachmentPath = _
   zrs.Fields("thefieldwithattachmentpathhere")
Set objOutlookAttach = _
   .Attachments.Add(AttachmentPath)
zrs.MoveNext
Loop
'
'cleanup code would follow

Once again, this is untested and based upon the code from MS from the above link.

-z

[TheSmileyCoder]

Here is some code I use to attach anywhere from 2 to 40 various files to an email.

I have had to remove certain bits from the code that wouldn't make sense in this context, so if the code is broken I probably removed to much.

Public Function CreateEmail() As Boolean
'On Error GoTo errHandler

   Dim rsFiles As DAO.Recordset
   Set rsFiles = CurrentDb.OpenRecordset("SELECT * from tbl_ReviewCommentSheet where bChecked ORDER BY tx_TransmittalID, RID", dbOpenSnapshot, dbReadOnly)
   
   'If nothing selected exit
      If rsFiles.EOF Then GoTo ExitFunction
   
   'Open outllook and start email
      Dim oMail As Outlook.MailItem
      Dim oApp As Outlook.Application
      Set oApp = New Outlook.Application
      Set oMail = oApp.CreateItem(olMailItem)
      oMail.HTMLBody = "<H3><B>Dear Sirs</B></H3>" & _
              "Please find enclosed our comments to the above mentioned Transmittals.<br><br>" & _
               GetSignature 'GetSignature custom function to load users Auto-signature
      Dim oRecipient As Outlook.Recipient
      'Add the recipients
         Set oRecipient = oMail.Recipients.Add("FakeName@FakeCompany.com")
         oRecipient.Type = olTo
      
         Set oRecipient = oMail.Recipients.Add("FakeName2@FakeCompany2.dk")
         oRecipient.Type = olCC
      oMail.Recipients.ResolveAll
      
   'Loop through adding attachments
      Dim strPath As String
      Dim strWorkPath As String
      strWorkPath = "G:\DATA\Comments to transmittals\LetterCreator\WorkFolder\"
      Dim strLastTrans As String
      Dim strSubject As String
      strSubject = "Comments to Transmittals"
      Do While Not rsFiles.EOF
         If strLastTrans <> rsFiles!tx_TransmittalID Then
            strLastTrans = rsFiles!tx_TransmittalID
            strSubject = strSubject & " " & rsFiles!tx_TransmittalID & ","
         End If
         
         
      
         strPath = "G:\DATA\Comments to transmittals\Pending\" & rsFiles!tx_TransmittalID & "\"
         'Excel first
            oMail.Attachments.Add strPath & rsFiles!tx_ExcelFile, olByValue
            'now pdf
            oMail.Attachments.Add strWorkPath & rsFiles!tx_PdfFile, olByValue
            Kill strWorkPath & rsFiles!tx_PdfFile
         rsFiles.MoveNext
      Loop
   strSubject = Left(strSubject, Len(strSubject) - 1)
   oMail.Subject = strSubject
   oMail.Display
   CreateEmail = True
   
ExitFunction:
   rsFiles.Close
   Set rsFiles = Nothing
   Exit Function
   
exitFail:
   On Error Resume Next
   GoTo ExitFunction
   
errHandler:
   MsgBox Err.Number & " - " & Err.Description
   Resume exitFail
End Function

[twinnyfo]

Smiley - you beat me to the punch. That was the type of code I was looking for. It's the only way I know to include multiple attachments.... Thanks!

[TheSmileyCoder]

The key bit here is the

oMail.Attachments.Add strWorkPath & rsFiles!tx_PdfFile, olByValue

Using that several times you can in theory add quite alot of attachments. That said, you will probably hit the limit of how large emails your provider allows to send, or the receiver is allowed to receive.

The olByValue means that a copy of the object is being added to the email, after which you can delete the original object, even if the email isn't send yet. If you don't include this, I BELIEVE that the copy of the object isn't actually done until you call the SEND. In the code I displayed the email is displayed for the user, for the user to confirm, and manually click the send button.

To place it into the outbox immediately replace

oMail.Display

with

oMail.Send