Email multiple files (1 Viewer)

KenHigg

Registered User
Local time
Today, 02:57
Joined
Jun 9, 2004
Messages
13,327
I think this was covered a while back and I did a search and couldn't find anything on it; Is there a way to attach and send multiple report .snp's to one email. I've been using SendObject.

thanks -
 

rsmonkey

Registered User.
Local time
Yesterday, 23:57
Joined
Aug 14, 2006
Messages
298
had this problem before and nfortunately didnt find a decent soloution but i did find some messy work arounds..

1. http://www.fabalou.com/Access/Reports/RTFtoWord.asp

its messy but it did work but i ended up not using it in the end.. it baisically sticks all reports into a single word document and sends that..

2. Alternately I used the same concept but set it up so it outputted the reports into a single .PDF file.

Neither are that good soloutions but it was all i could do in the time frame!
 

Moniker

VBA Pro
Local time
Today, 01:57
Joined
Dec 21, 2006
Messages
1,567
SendObject will not allow you to attach more than one object. You'll need to use the Outlook object (search here for that) to attach more than one external file. You attach to the Outlook object the same way you attach to any external application (declare it, set it, etc.). Again, a search of this forum will show you the details if you don't know what I mean.
 

boblarson

Smeghead
Local time
Yesterday, 23:57
Joined
Jan 12, 2001
Messages
32,059
Ken - I'll post you some code I'm using to send multiple attachments as soon as I get to work this morning.
 

rsmonkey

Registered User.
Local time
Yesterday, 23:57
Joined
Aug 14, 2006
Messages
298
pfft outlook... 'i fart in your general direction!'
 

KenHigg

Registered User
Local time
Today, 02:57
Joined
Jun 9, 2004
Messages
13,327
Ken - I'll post you some code I'm using to send multiple attachments as soon as I get to work this morning.

Cool - Thanks

Meanwhile I'll look into using the outlook object...
 

Moniker

VBA Pro
Local time
Today, 01:57
Joined
Dec 21, 2006
Messages
1,567
Ken,

Here you go. Remove the line in red if you don't want the email to display, and uncomment the .Send to automatically send it. You'll need the Microsoft Outlook X.0 Object Library reference added to your DB for this to work. (The X is the version of Outlook, which is 11 in Office 2003. Yours may vary.)

Code:
Sub EmailTest()

    Dim OLApp As Outlook.Application
    Dim OLMsg As Outlook.MailItem
    
    Set OLApp = New Outlook.Application
    Set OLMsg = OLApp.CreateItem(olMailItem)
    
    With OLMsg
        [COLOR="Red"].Display[/COLOR]
        .To = "abc@xyz.com"
        .Subject = "test email"
        .Body = "This is the body of the email."
        .Attachments.Add "Full path to attachment 1"
        .Attachments.Add "Full path to attachment 2"
        .Attachments.Add "Full path to attachment X"
[COLOR="Green"]'        .Send    <--- Uncomment this to send the email[/COLOR]
    End With

    Set OLMsg = Nothing
    Set OLApp = Nothing

End Sub
 

KenHigg

Registered User
Local time
Today, 02:57
Joined
Jun 9, 2004
Messages
13,327
Ken,

Here you go. Remove the line in red if you don't want the email to display, and uncomment the .Send to automatically send it. You'll need the Microsoft Outlook X.0 Object Library reference added to your DB for this to work. (The X is the version of Outlook, which is 11 in Office 2003. Yours may vary.)

Code:
Sub EmailTest()

    Dim OLApp As Outlook.Application
    Dim OLMsg As Outlook.MailItem
    
    Set OLApp = New Outlook.Application
    Set OLMsg = OLApp.CreateItem(olMailItem)
    
    With OLMsg
        [COLOR="Red"].Display[/COLOR]
        .To = "abc@xyz.com"
        .Subject = "test email"
        .Body = "This is the body of the email."
        .Attachments.Add "Full path to attachment 1"
        .Attachments.Add "Full path to attachment 2"
        .Attachments.Add "Full path to attachment X"
[COLOR="Green"]'        .Send    <--- Uncomment this to send the email[/COLOR]
    End With

    Set OLMsg = Nothing
    Set OLApp = Nothing

End Sub

Cool - Thanks a million - :)
 

boblarson

Smeghead
Local time
Yesterday, 23:57
Joined
Jan 12, 2001
Messages
32,059
Just to follow through with what I said I would do -

Here's my code for getting attachments and putting the list in a text box:
Code:
Private Sub cmdAttachments_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant


    ' Set up the File dialog box.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        ' Allow the user to make multiple selections in the dialog box.
        .AllowMultiSelect = True

        ' Set the title of the dialog box.
        .Title = "Select One or More Files to Attach"

        ' Clear out the current filters, and then add your own.
        .Filters.Clear
        '      .Filters.Add "Access Databases", "*.MDB"
        '      .Filters.Add "Access Projects", "*.ADP"
        .Filters.Add "All Files", "*.*"

        ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
        If .Show = True Then
            ' Loop through each file that is selected and then add it to the list box.
            For Each varFile In .SelectedItems
                If Nz(Me.txtAttachments, "") & "" = "" Then
                    Me.txtAttachments = varFile
                Else
                    Me.txtAttachments = Me.txtAttachments & ";" & varFile
                End If
            Next
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
    End With
End Sub

And then the code to send:
Code:
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim varSplit As Variant
    Dim intCounter As Integer
    Dim bDisplayMsg As Boolean
    Dim sAttachmentPath As String
    Dim varAttachSplit As Variant

   On Error GoTo err_handler
    Screen.MousePointer = 11
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
        ' Add the To recipient(s) to the message.
        varSplit = Split(Nz(Me.txtTo, ""), ";", , vbTextCompare)
        Do Until intCounter = UBound(varSplit) + 1
            Set objOutlookRecip = .Recipients.Add(varSplit(intCounter))
            objOutlookRecip.Type = olTo
            intCounter = intCounter + 1
        Loop

        intCounter = 0
        
        varSplit = Split(Nz(Me.txtCC, ""), ";", , vbTextCompare)
        Do Until intCounter = UBound(varSplit) + 1
            ' Add the CC recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add(varSplit(intCounter))
            objOutlookRecip.Type = olCC
            intCounter = intCounter + 1
        Loop
        intCounter = 0
        ' Add the BCC recipient(s) to the message.
        varSplit = Split(Nz(Me.txtBCC, ""), ";", , vbTextCompare)
        Do Until intCounter = UBound(varSplit) + 1
            Set objOutlookRecip = .Recipients.Add(varSplit(intCounter))
            objOutlookRecip.Type = olBCC
            intCounter = intCounter + 1
        Loop
        ' Set the Subject, Body, and Importance of the message.
        .Subject = Me.txtSubject
        .Body = Me.txtBody
        Select Case Me.cboImportance
        Case "Low"
            .Importance = olImportanceLow
        Case "Normal"
            .Importance = olImportanceNormal
        Case "High"
            'High importance
            .Importance = olImportanceHigh
        End Select

        sAttachmentPath = Nz(Me.txtAttachments, "")
        ' Add attachments to the message.
        If Not IsMissing(sAttachmentPath) Then
            varAttachSplit = Split(sAttachmentPath, ";", , vbTextCompare)
            intCounter = 0
            Do Until intCounter = UBound(varAttachSplit) + 1
                Set objOutlookAttach = .Attachments.Add(varAttachSplit(intCounter))
                intCounter = intCounter + 1
            Loop
        End If

        '             ' Resolve each Recipient's name.
        '             For Each objOutlookRecip In .Recipients
        '                 objOutlookRecip.Resolve
        '             Next

        ' Should we display the message before sending?
        If bDisplayMsg Then
            .Display
        Else
            .Save
            .Send
            Screen.MousePointer = 1
            MsgBox "Message has been sent", vbInformation, "Email Sent"
        End If
    End With
    Set objOutlook = Nothing
    DoCmd.Close acForm, Me.Name, acSaveNo

Exit_cmdSendEmail_Click:
   Exit Sub

err_handler:
    Screen.MousePointer = 1
    DoCmd.SetWarnings True
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSendEmail_Click of VBA Document Form_frm_SendEmail"
    Resume Exit_cmdSendEmail_Click

End Sub
 

Dylan Snyder

Registered User.
Local time
Today, 01:57
Joined
Dec 31, 2007
Messages
89
In the attachments of the email, I'm looking to send both an access report and a text file, for the text file, I can just insert the windows path. To attach the access report to the same email, how would I do this. I copied the code listed above and it works, but when I attach a "sendobject" it just generates two emails.
 

boblarson

Smeghead
Local time
Yesterday, 23:57
Joined
Jan 12, 2001
Messages
32,059
Maybe send the report first to a folder where you can then pick it up as a windows path in the email?
 

hoang0501

New member
Local time
Yesterday, 23:57
Joined
Feb 2, 2010
Messages
1
what is the name of function in "And then the code to send:"
the code of sending email is put in module, isn't it ?
 

julia55

Registered User.
Local time
Today, 01:57
Joined
May 2, 2012
Messages
10
Can you tell me how to add the Outlook Object Library reference to me DB? I am using Outlook and Access 2007. Please note that I am extremely new to Access and VBA so please explain in newbie terms :)

Ken,

Here you go. Remove the line in red if you don't want the email to display, and uncomment the .Send to automatically send it. You'll need the Microsoft Outlook X.0 Object Library reference added to your DB for this to work. (The X is the version of Outlook, which is 11 in Office 2003. Yours may vary.)

Code:
Sub EmailTest()
 
    Dim OLApp As Outlook.Application
    Dim OLMsg As Outlook.MailItem
 
    Set OLApp = New Outlook.Application
    Set OLMsg = OLApp.CreateItem(olMailItem)
 
    With OLMsg
        [COLOR=red].Display[/COLOR]
        .To = "abc@xyz.com"
        .Subject = "test email"
        .Body = "This is the body of the email."
        .Attachments.Add "Full path to attachment 1"
        .Attachments.Add "Full path to attachment 2"
        .Attachments.Add "Full path to attachment X"
[COLOR=green]'        .Send    <--- Uncomment this to send the email[/COLOR]
    End With
 
    Set OLMsg = Nothing
    Set OLApp = Nothing
 
End Sub
 

Beetle

Duly Registered Boozer
Local time
Today, 00:57
Joined
Apr 30, 2011
Messages
1,808
Open a VBA code window (a simple way to do this is with Alt + F11 when you have your app open). Once the VBA window is open go to Tools/References;




You will then see the References window. Scroll down until you find the Microsoft Outlook X.0 Object Library (in A2007 it should be 12.0) and check it, then click OK;

 

Attachments

  • Code window.JPG
    Code window.JPG
    68.3 KB · Views: 6,736
  • Reference window.jpg
    Reference window.jpg
    96.3 KB · Views: 6,271

julia55

Registered User.
Local time
Today, 01:57
Joined
May 2, 2012
Messages
10
Thank you so much Beetle! That was exactly what I needed and fixed my problem, now my email is sending!
 

raghuprabhu

Registered User.
Local time
Yesterday, 23:57
Joined
Mar 24, 2008
Messages
154
Hi Every one,

I have two attachments to be sent out in my emails. I am unable to send multiple attachments. So I have combined both the documents and sending one bulky document out to people who have not supplied their Medicare Levi Variation form and/or Employment declaration form

How do I modify the following code to send individual documents? If the member has supplied Medicare Levi variation form, the I want to send him Employment declaration form or if the member has supplied Employment declaration form then I want to send him only Medicare Levi Variation form. If neither has been received I want to send both.

Code:
 Function EmailsSendToAll() As Boolean
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim s As String
Dim sSQL As String
Dim sSubject As String
Dim sBody As String
Dim sBodyBegin As String
Dim sGreeting As String
Dim sDOE As String
Dim sMessageTFN As String
Dim sMessageMedicare As String
Dim sMessageEmployment As String
Dim sSignature As String
Dim sAttachPath As String
Dim sBodyEnd As String
Dim activeDir As String
         activeDir = CurrentProject.path
            
        sSignature = vbCrLf _
            & "My signature block goes here"    
         
        
        sSQL = "Query for my data to send the Emails go there"
 'Debug.Print sSQL
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
    .Open sSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
    .MoveLast 'force error 3021 if no records
    .MoveFirst
    
    Do Until .EOF
    
        sMessageTFN = vbCrLf _
            & "No TFN Message goes here"
        
        sMessageMedicare = vbCrLf _
            & "No Medicare form message goes here"
        
        sMessageEmployment = vbCrLf _
            & "No Employment Declaration form message goes here"
            
    
    
        sGreeting = Greeting goes here" & vbCrLf
            s = s & sGreeting
                                                                                                                        
        
 
            If .Fields("Medicare") = False Or .Fields("Employment") = False Or .Fields("TFNDeclaration") = False Then
                 If .Fields("TFNDeclaration") = False Then
                    s = s & sMessageTFN
                End If
                 If .Fields("Medicare") = False Then
                    s = s & sMessageMedicare
                    sAttachPath = activeDir & "\SampleForm.pdf" '<<<<<<<<<<<<<<<<
                End If
                 If .Fields("Employment") = False Then
                    s = s & sMessageEmployment
                    sAttachPath = activeDir & "\SampleForm.pdf" '<<<<<<<<<<<<<<<<
                End If
                
            End If
            
            s = s & sSignature
             If SendEmail(.Fields("txtTitles"), .Fields("txtSurname"), "Missing tax details", s, sAttachPath) Then
            
                'Do nothing...
             Else
                MsgBox "Email to " & .Fields("txtTitles") & " " & .Fields("txtSurname") & " failed.", vbInformation, "Send Failure"
            End If
                
        .MoveNext
    
    Loop
    DoCmd.OpenForm "frmTFN", acNormal
 End With
 rs.Close
EmailsSendToAll = True
GoTo ThatsIt
ErrorHandler:
    Select Case Err.Number
        Case -2147217908 'command text not set
        Case -2147217865 'cannot find table
        Case 3021 'no records
        Case Else
            MsgBox "Problem with EmailsSendToAll()" & vbCrLf _
                 & "Error " & Err.Number & ": " & Err.Description
    End Select
    EmailsSendToAll = False
ThatsIt:
If Not rs Is Nothing Then Set rs = Nothing
conn.Close
End Function
 

Users who are viewing this thread

Top Bottom