Adding attachments to email, saving attachments

[snsmith]

I am very new to VBA but have been doing some stuff with general Access macros for a while. I have multiple items I'm trying to do with the push of a button. I can do some things separate (with help from Macros and the internet) but having a very hard time combining them.

I currently have one query that pulls all my "InvoicingData" that is used in my "Invoice" report. I have an "InvoicingDataSummary" query that houses all the information in my "InvoicingData" query but it's grouped so I can gather my invoice totals. I am trying to accomplish all these things and if I have to do it in three steps I will, but I also need to know the best way:

1) Send an email to everyone on my "InvoicingDataSummary" query
2) Attach the invoice for the same study to the email (step #1), rename it to the Invoice#. I can do this for each record as I click on a particular button (code below), but I just can't see to make it cycle through the entire report. It would have to be opened and closed for each invoice to refresh the data.
3) Save the attachment to a particular folder path and rename it to the Invoice#

********
#1
I am able to correctly and accurately cycle through my "InvoicingDataSummary" for emails using the programming below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String



Dim emailTo As String
Dim emailCC As String
Dim emailSubject As String
Dim emailText As String
Dim smsg As String

Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem
Dim outStarted As Boolean

Dim qdf As DAO.QueryDef
Dim prm As Parameter


On Error Resume Next
Set outApp = GetObject(, "Outlook.application")
On Error GoTo 0

If outApp Is Nothing Then
Set outApp = CreateObject("Outlook.application")
outStarted = True
End If




'get data
Set db = CurrentDb


Debug.Print strSQL

Set qdf = db.QueryDefs("InvoiceDataSummary")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset




'send email

Do Until rs.EOF

emailText = ""
emailTo = rs.Fields("[PI Email]").Value & ";" & rs.Fields("[Fiscal Contact Email]").Value


emailSubject = rs.Fields("[PI Name]").Value & ", Your invoice for Study " & rs.Fields("[Study ID]").Value & " has arrived." & vbCrLf

smsg = "Hello, " & vbCrLf
smsg = smsg & "The " & rs.Fields("[Fiscal Year]").Value & " for " & rs.Fields("[Month Invoiced]").Value & " is attached." & vbCrLf
smsg = smsg & " Invoice Number: " & rs.Fields("[Invoice #]").Value & vbCrLf
smsg = smsg & " Amount of Invoice: " & Format(rs.Fields("[Amount of Invoice]").Value, "Currency") & vbCrLf

smsg = smsg & vbCrLf
smsg = smsg & " Thank you," & vbCrLf

emailText = smsg

Set outMail = outApp.CreateItem(olMailItem)
outMail.To = emailTo
outMail.CC = emailCC
outMail.Subject = emailSubject
outMail.Body = emailText
outMail.Display


rs.MoveNext

Loop
MsgBox "All Emails Have Been Drafted"


rs.Close
Set rs = Nothing
Set db = Nothing


If outStarted Then
outApp.Quit
End If


Set outMail = Nothing
Set outApp = Nothing




End Sub

**********
#2
I am able to get the Invoice# set, renamed and added to an email but unable to pull any data into it from my "InvoiceDataSummary" query:
Private Sub Send_Invoice_Click()
Dim sExistingReportName As String
Dim sAttachmentName As String


sExistingReportName = "Invoice"
sAttachmentName = [Invoice #].Value


DoCmd.OpenReport sExistingReportName, acViewReport, "", "[Study ID]=" & [Study ID], acHidden
Reports(sExistingReportName).Caption = sAttachmentName


DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, "", "", "", "", "", True, ""


End Sub
******
I've tried multiple renditions but not having much luck. Would appreciate any guidance!

Thanks!

[Minty]

You need to incorporate the report opening and saving into your email rs loop.
The logic flow is something along these lines

Open your recordset

In the loop
Open your filtered report
Save it with the specific file name as a pdf
Close the filtered report

Do your email bit and add the following

outMail.AddAttachment = Your full path to the saved pdf report

send or display the email

Loop around

[snsmith]

I'm trying to get these to work separately (so I can tweak it before getting it into my email file). This is what I have so far but it keeps telling me The OutputTo action was canceled. I've tried multiple tweaks to that line but same error.

Dim reportName As String
Dim fileName As String
Dim criteria As String

reportName = "Invoice"
fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

Now when I hover over the "DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName" the values show as I want them to.

[Welshgasman]

So show your values?

[Gicu]

My hunch is that your path is missing a back slash:

fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"

should probably be

fileName = "c:\Documents\InvoicesForApproval\" & [Invoice #].Value & ".pdf"

Cheers,

[snsmith]

So show your values?

I did add the backslash as was suggested by another. This is what my values are showing:
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName, , , , acExportQualityPrint
DoCMD.OutputTo acOutputReport=3, reportName = "Invoice", acFormatPDF="PDF Format (*.pdf)", "c:\Documents\InvoicesForApproval\SECIM08-78.pdf", , , , acExportQualityPrint=0

[Gicu]

You don't specify the report name in the Docmd.OutputTo line, that is the entire point of opening just before hidden:

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, , acFormatPDF, fileName 'leave the report name parameter blank
DoCmd.Close acReport, reportName, acSaveNo

Cheers,
Vlad

[snsmith]

You don't specify the report name in the Docmd.OutputTo line, that is the entire point of opening just before hidden:

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, , acFormatPDF, fileName 'leave the report name parameter blank
DoCmd.Close acReport, reportName, acSaveNo

Cheers,
Vlad

Sorry, I had the full code above and didn't copy and paste all of it. It was defined at the beginning. Here is my full code.
Dim reportName As String
Dim fileName As String
Dim criteria As String

reportName = "Invoice"
fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName, , , , acExportQualityPrint
DoCmd.Close acReport, reportName, acSaveNo

[Gicu]

So I as said in the previous post, you need to leave the report name parameter empty for the Docmd.OutputTo in order to export the currently open report (Invoice). Specifying the name will make the code to attempt to open it a second time (and it will be the full\unfiltered by your where clause).

Cheers,

[snsmith]

So sorry to have miss read that. I tried to remove the report name as suggested but then I get the Object Type argument for the action or method is left blank or invalid.
Newest changes:
Dim reportName As String
Dim fileName As String
Dim criteria As String

reportName = "Invoice"
fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, , acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

[Gicu]

I think you have one too many comma....

[snsmith]

When I remove the extra column it starts to generate this error, "The format in which you are attempting to output the current object is not available."

Dim reportName As String
Dim fileName As String
Dim criteria As String

reportName = "Invoice"
fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]

DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acOutputReport, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

[Minty]

The correct syntax for the output command is

DoCmd.OutputTo acReport, reportName , acFormatPDF, sFullPath, , , , acExportQualityPrint

[Gicu]

Could you please try this:

DoCmd.OutputTo acOutputReport,"", acFormatPDF, fileName

Cheers,

[snsmith]

So with the second change, I get the OutpuTo action was canceled:
Dim reportName As String
Dim fileName As String
Dim criteria As String


reportName = "Invoice"
fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]

DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo


If I do the first suggestion, it appears to drop the filter and will not rename the file. At least it does give me the prompt for the file location:
Dim reportName As String
Dim fileName As String
Dim criteria As String


reportName = "Invoice"
fileName = "c:\Documents\InvoicesForApproval" & [Invoice #].Value & ".pdf"
criteria = "[Study ID]=" & [Study ID]

DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acReport, reportName, acFormatPDF, sFullPath, , , , acExportQualityPrint
DoCmd.Close acReport, reportName, acSaveNo