Multiple Reports attached to an email

[MyWaterloo]

So, I have a database I'm using for Employee Time Sheets. The pay period is a two week cycle. There is one report for each week. I would like to just combine the two weeks into one report, but my company has this "interestin g" (say backwards) system that they don't want to change. So instead of filling out the time sheets by hand *gasp* (I know) I created a database (obviously). Anyway, how can I attach both reports to a single email? I have a button for emailing that will send both reports to my email, but each report creates its own message and attaches itself. Don't really want to send two emails for each employee. Thanks for your always mind blowing help.

[MyWaterloo]

Sorry. I am using office 2007. So outlook is my email app. Thanks

[MyWaterloo]

Just moving the thread back to the top hoping for an answer.

[nico5038]

For this you'll need to save the reports as a file and create an email with multiple attachments.

I assume you can create the needed files, here (part of) the Microsoft sample code for creating outlook emails with attachments.

Sub CallCreateMail()
    ' This procedure shows how to pass an array of recipients,
    ' a subject line, a message body, and an array of attachments
    ' to the CreateMail procedure.
    Dim strSubject      As String
    Dim strBody         As String
    Dim avarRecip(2)    As Variant
    Dim avarAttach(2)   As Variant
    
    ' Create recipients array.
    avarRecip(0) = "Maria Anders"
    avarRecip(1) = "Ana Trujillo"
    avarRecip(2) = "Antonio Moreno"
    
    ' Create attachments array.
    avarAttach(0) = "c:\bootlog.txt"
    avarAttach(1) = "c:\autoexec.bat"
    avarAttach(2) = "c:\config.sys"
    
    ' Create Subject line and message body.
    strSubject = "This is the subject line."
    strBody = "If not now, when? If not you, who?"
    
    If CreateMail(avarRecip, strSubject, strBody, avarAttach) = True Then
        MsgBox "Congratulations! Your mail has been sent."
    End If

Function CreateMail(astrRecip As Variant, _
                    strSubject As String, _
                    strMessage As String, _
                    Optional astrAttachments As Variant) As Boolean
    ' This procedure illustrates how to create a new mail message
    ' and use the information passed as arguments to set message
    ' properties for the subject, text (Body property), attachments,
    ' and recipients.

    Dim objNewMail          As Outlook.MailItem
    Dim varRecip            As Variant
    Dim varAttach           As Variant
    Dim blnResolveSuccess   As Boolean
    
    On Error GoTo CreateMail_Err
    
    ' Use the InitializeOutlook procedure to initialize global
    ' Application and NameSpace object variables, if necessary.
    If golApp Is Nothing Then
        If InitializeOutlook = False Then
            MsgBox "Unable to initialize Outlook Application " _
                & "or NameSpace object variables!"
            Exit Function
        End If
    End If

    Set golApp = New Outlook.Application
    Set objNewMail = golApp.CreateItem(olMailItem)
    With objNewMail
        For Each varRecip In astrRecip
            .Recipients.Add varRecip
        Next varRecip
        blnResolveSuccess = .Recipients.ResolveAll
        For Each varAttach In astrAttachments
            .Attachments.Add varAttach
        Next varAttach
        .Subject = strSubject
        .Body = strMessage
        If blnResolveSuccess Then
            .Send
        Else
            MsgBox "Unable to resolve all recipients. Please check " _
                & "the names."
            .Display
        End If
    End With
    
    CreateMail = True

CreateMail_End:
    Exit Function
CreateMail_Err:
    CreateMail = False
    Resume CreateMail_End
End Function

More code can be found at the Microsoft site.

Nic;o)

[MyWaterloo]

Thanks Nico,
I guess I was just hoping there might be some easier way before I dive into manipulating Outlook (I have yet to sail those waters). But...I guess not. Thanks again.

[nico5038]

Hmm, you might try to create one report with both weekly reports as sub reports, but that will probably give some page layout problems. The combined report can be mailed "straight away"

Nic;o)