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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.