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!