Trying to add two attachments to an email

I'm trying to add two attachments to an Outlook email.  But the following code gets stuck at:

.Attachments.Add aFile

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim fileNameIncomeStatement As String, todayDateIncomeStatement As String
    Dim fileNameSalesAnalysis As String, todayDateSalesAnalysis As String
    Dim rptIncomeStatement As Object
    Dim rptSalesAnalysis As Object
    Dim FileArray As String
    Dim AttachmentFiles, aFile
    
    todayDateIncomeStatement = Format(Date, "MMDDYYYY")
    todayDateSalesAnalysis = Format(Date, "MMDDYYYY")
    
    'Email Reports...
    fileNameIncomeStatement = Application.CurrentProject.Path & "\Income Statement_" & todayDateIncomeStatement & ".pdf"
    DoCmd.OutputTo acReport, "Income Statement", acFormatPDF, fileNameIncomeStatement, , , , acExportQualityPrint

    DoCmd.OpenReport "Income Statement", acViewPreview, , , acHidden
    Reports![Income Statement].Visible = False


    fileNameSalesAnalysis = Application.CurrentProject.Path & "\Sales Analysis_" & todayDateSalesAnalysis & ".pdf"
    DoCmd.OutputTo acReport, "Sales Analysis", acFormatPDF, fileNameSalesAnalysis, , , , acExportQualityPrint

    DoCmd.OpenReport "Sales Analysis", acViewPreview, , , acHidden
    Reports![Sales Analysis].Visible = False


    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
    
    FileArray = "fileNameSalesAnalysis, fileNameIncomeStatement,"

    AttachmentFiles = Split(FileArray, ",")
    
        .Recipients.Add Reports![Income Statement].txtEMail
        .Subject = "Income Statement and Sales Analysis Attached for Branch " & Reports![Income Statement].txtBranch & " for " & Reports![Income Statement].txtStartDate & " - " & Reports![Income Statement].txtEndDate
        .Body = "Income Statement and Sales Analysis Attached"
        
        For Each aFile In AttachmentFiles
           .Attachments.Add aFile
        Next

        .Display
        '.Send
    End With

    DoCmd.Close acReport, "Income Statement", acSaveNo
    DoCmd.Close acReport, "Sales Analysis", acSaveNo

Open in new window

SteveL13Asked:
Who is Participating?
 
Bill PrewCommented:
Try this:

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim fileNameIncomeStatement As String, todayDateIncomeStatement As String
    Dim fileNameSalesAnalysis As String, todayDateSalesAnalysis As String
    Dim rptIncomeStatement As Object
    Dim rptSalesAnalysis As Object
    Dim FileArray As String
    Dim AttachmentFiles, aFile
    
    todayDateIncomeStatement = Format(Date, "MMDDYYYY")
    todayDateSalesAnalysis = Format(Date, "MMDDYYYY")
    
    'Email Reports...
    fileNameIncomeStatement = Application.CurrentProject.Path & "\Income Statement_" & todayDateIncomeStatement & ".pdf"
    DoCmd.OutputTo acReport, "Income Statement", acFormatPDF, fileNameIncomeStatement, , , , acExportQualityPrint

    DoCmd.OpenReport "Income Statement", acViewPreview, , , acHidden
    Reports![Income Statement].Visible = False


    fileNameSalesAnalysis = Application.CurrentProject.Path & "\Sales Analysis_" & todayDateSalesAnalysis & ".pdf"
    DoCmd.OutputTo acReport, "Sales Analysis", acFormatPDF, fileNameSalesAnalysis, , , , acExportQualityPrint

    DoCmd.OpenReport "Sales Analysis", acViewPreview, , , acHidden
    Reports![Sales Analysis].Visible = False


    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
    
        .Recipients.Add Reports![Income Statement].txtEMail
        .CC = "accounting@travelsociety.com"
        .Subject = "Financial Statement for " & Reports![Income Statement].txtStartDate & " - " & Reports![Income Statement].txtEndDate
        .Body = "Attached are both your Income Statement and Sales Analysis.  If you have any questions please contact Susanne at susannet@travelsociety.com or 303 563 6253."
        
        .Attachments.Add fileNameSalesAnalysis
        .Attachments.Add fileNameIncomeStatement

        .Display
        '.Send
    End With

    DoCmd.Close acReport, "Income Statement", acSaveNo
    DoCmd.Close acReport, "Sales Analysis", acSaveNo

Open in new window


»bp
0
 
Bill PrewCommented:
Two things I notice.

FileArray = "fileNameSalesAnalysis, fileNameIncomeStatement,"

You have a trailing comma, so will actually generate three elements from that string when you split it, the last will ne empty and throw an error.  Get rid of the trailing comma.

Also, are those real file names?  And you may need to make them the full path to the file.


»bp
0
 
Fabrice LambertFabrice LambertCommented:
I think you confused string values and variables names, as a result, your FileArray variable doesn't hold any path.

I would directly make an array with your two variables:
AttachmentFiles = array(fileNameSalesAnalysis, fileNameIncomeStatement)

Open in new window

Get rid of the FileArray variable as it is now useless, and remove any line using it.
Also, declare the AttachmentFiles variable as a dynamic array of variants.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
as Frabrice was saying, forget the array and just assign the files:

           .Attachments.Add fileNameSalesAnalysis
           .Attachments.Add fileNameIncomeStatement

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I would also suggest that you make your code more readable and easier to maintain by using a naming convention.   Typically for variables, you would but the variable type in front of the name. i.e.:

strFileNameSalesAnalysis
strFileNameIncomeStatement

 'int' = integer, 'lng' = long, 'obj' is Object, etc.

 This helps you in that:

1. You don't do things like mix up string and numbers.
2. Avoid moving data between incompatible types.....      intCount = lngLastRecord   (a long may not fit in an integer).
3. It is obvious that it is a variable rather than an object, like a control.

 Keep it up though...you've come a long way!

Jim.
0
 
Bill PrewCommented:
So you could fix your existing approach by doing:

FileArray = fileNameSalesAnalysis & "," & fileNameIncomeStatement

But since you already have the strings as separate values putting them together as a string only to then Split() them into an array seems like extra work, so just creating an array from the values as Fabrice mentioned seems reasonable.

I can see some value to the array approach if you think there could be more (or less) attachments in the future, the code might be a little easier to change with the array approach.  But if you don't expect that to change over time then as Jim mentioned skipping the array all together is probably the simplest approach.

Let us know if you still have problems.


»bp
0
 
SteveL13Author Commented:
Here is where I'm at so far.  But now the issue is that I'm getting two of each report in the email attachments.

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim fileNameIncomeStatement As String, todayDateIncomeStatement As String
    Dim fileNameSalesAnalysis As String, todayDateSalesAnalysis As String
    Dim rptIncomeStatement As Object
    Dim rptSalesAnalysis As Object
    Dim FileArray As String
    Dim AttachmentFiles, aFile
    
    todayDateIncomeStatement = Format(Date, "MMDDYYYY")
    todayDateSalesAnalysis = Format(Date, "MMDDYYYY")
    
    'Email Reports...
    fileNameIncomeStatement = Application.CurrentProject.Path & "\Income Statement_" & todayDateIncomeStatement & ".pdf"
    DoCmd.OutputTo acReport, "Income Statement", acFormatPDF, fileNameIncomeStatement, , , , acExportQualityPrint

    DoCmd.OpenReport "Income Statement", acViewPreview, , , acHidden
    Reports![Income Statement].Visible = False


    fileNameSalesAnalysis = Application.CurrentProject.Path & "\Sales Analysis_" & todayDateSalesAnalysis & ".pdf"
    DoCmd.OutputTo acReport, "Sales Analysis", acFormatPDF, fileNameSalesAnalysis, , , , acExportQualityPrint

    DoCmd.OpenReport "Sales Analysis", acViewPreview, , , acHidden
    Reports![Sales Analysis].Visible = False


    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
    
    FileArray = "fileNameSalesAnalysis, fileNameIncomeStatement"
'
    AttachmentFiles = Split(FileArray, ",")
    
        .Recipients.Add Reports![Income Statement].txtEMail
        .CC = "accounting@travelsociety.com"
        .Subject = "Financial Statement for " & Reports![Income Statement].txtStartDate & " - " & Reports![Income Statement].txtEndDate
        .Body = "Attached are both your Income Statement and Sales Analysis.  If you have any questions please contact Susanne at susannet@travelsociety.com or 303 563 6253."
        
        For Each aFile In AttachmentFiles
            .Attachments.Add fileNameSalesAnalysis
            .Attachments.Add fileNameIncomeStatement
        Next

        .Display
        '.Send
    End With

    DoCmd.Close acReport, "Income Statement", acSaveNo
    DoCmd.Close acReport, "Sales Analysis", acSaveNo

Open in new window

0
 
Fabrice LambertFabrice LambertCommented:
1st: You're still splitting a string holding two variable names instead of path (we recommended to get rid of this).
2nd: For each items in the resulting array, you attach your two files. Enhence why files are attached twice (Two item in the array mutiplied by two attachments).
0
 
SteveL13Author Commented:
The latest solution worked.  (As proposed by a couple of you.)  I'm going to be posting another question regarding all of this now that the suggestion worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.