Hi- I'm using the below VBA code to generate emails for each of our open initiatives (projects). The below code runs via a button and it creates an email with a report attached for the first initiative that is either saved to draft or sent and then the next initiative email opens... It is currently running fine, but I need help adding the email addresses (InitiativePOCs.EmailAddress) for each initiative's POCs to the email as well as the POCs' first names (InitiativePOCs.FirstName) to the email body.
Here's a screen shot of the relationship of the tables:
The Initiative table contains the initiative information and key field is InitiativeID. The InitiativePOCs table contains each of our POCs with contact information and key field is ID. The InitiativePOC_Link table creates the link between these two tables and allows us to select many POCs for a single initiative while also selecting a POCType for each POC on each initiative as well.
I would like to add the POCs associated with the initiative that have POCType as "AOR" or "Alt. AOR" to the generated emails with the InitiativePOCs.EmailAddress field in the TO line of the email and the InitiativePOCs.FirstName field in the body of the email message.
Here's the code, please let me know if I've left off any important information to help with this issue. Thank you in advance for your help!!
Code:
Private Sub Command34_Click()
'creates draft email of all outstanding Reports individually by Initiative with Rich Text Format attachment of report
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim SigString As String
Dim Signature As String
strUserName = Environ("UserName") 'Windows UserName pulled from windows
strRptName = "AORMSSignOffReport"
SigString = "C:\Users\" & Environ("username") & _
"\AppData\Roaming\Microsoft\Signatures\New.txt" 'Outlook signature must be named "New"
'Used to create signature for email body
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
'Used to create a Recordset of InitiativeIDs for Form Filtering for each
'Initiative. [InitiativeID] is the Primary Key making the job easy
strSQL = "Select * From [AORMilestoneSignOff];"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
With MyRS
Do While Not MyRS.EOF
'Open the Report Filering by the WHERE Clause for each specific [InitiativeID] Value
DoCmd.OpenReport strRptName, acViewPreview, , "[InitiativeID] = " & ![InitiativeID]
'Output Reports for each PK ([InitiativeID])
On Error Resume Next
DoCmd.SendObject acSendReport, strRptName, acFormatRTF, , , , "Need AOR Milestone Approval for " & ![Initiative], "Just following up on the below request for milestone approval on " & ![Initiative] & ". Once you have reviewed, please complete the attached AOR approval form and either email it back to me or fax it to 843-207-6094. Thank you." & vbNewLine & vbNewLine & Signature
On Error GoTo 0
'Close each Report after Outputting
DoCmd.Close acReport, strRptName, acSaveNo
.MoveNext 'Move to the next Record in Recordset
Loop
End With
MyRS.Close
Set MyRS = Nothing
End Sub