Results 1 to 7 of 7

Automated Email - Need Help with code to add Email Addresses to recordset code

  1. #1
    MattSC is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    11

    Automated Email - Need Help with code to add Email Addresses to recordset code

    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:

    Click image for larger version. 

Name:	INITPOCLink.jpg 
Views:	31 
Size:	19.6 KB 
ID:	15914
    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

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Your docmd.sendobject is missing some stuff, no?

    Does the following snippit help?

    Code:
    Dim strSubject As String
    Dim strBody As String
    Dim strReport As String
    Dim strTo As String
    Dim strCc As String
    Dim strBcc As String
    strSubject = "Insert Subject Line Here"
    strBody = "Insert text to be displayed in the email body"
    strReport = "rptEquip"
    strTo = "you@gmail.com"
    strCc = ""
    strBcc = "me@gmail.com"
    DoCmd.SendObject acSendReport, strReport, acFormatTXT, strTo, strCc, strBcc, strSubject, strBody, False

  3. #3
    MattSC is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    11
    Thanks ItsMe. I've altered the code to your suggestion above.

    Below is the new code. What do I need to add in the strTo = "" to pull the POC email addresses from the InitiativePOCs table? (relationship in original post)

    Code:
    Code:
    Private Sub Command34_Click()
    
    'creates draft email of all outstanding AOR Sign Off 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 strSubject As String
        Dim stReport As String
        Dim strBody As String
        Dim strTo As String
        Dim strCC As String
        Dim SigString As String
        Dim Signature As String
            
        strUserName = Environ("UserName") 'Windows UserName pulled from windows
        strReport = "AORMSSignOffReport"
        strCC = "matt.glavach@scra.org;anna.pearson@scra.org"
        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 strReport, acViewPreview, , "[InitiativeID] = " & ![InitiativeID]
                
            strTo = ""
            strSubject = "Need AOR Milestone Approval for " & ![Initiative]
            strBody = "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
                
            'Output Reports for each PK ([InitiativeID])
            On Error Resume Next
            DoCmd.SendObject acSendReport, strReport, acFormatRTF, strTo, strCC, , strSubject, stBody
            On Error GoTo 0
         
            'Close each Report after Outputting
            DoCmd.Close acReport, strReport, acSaveNo
              .MoveNext       'Move to the next Record in Recordset
          Loop
        End With
         
        MyRS.Close
        Set MyRS = Nothing
    
    End Sub

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I am going to guess you have a query named "AORMilestoneSignOff" that you are using for your recordset. Is the field name EmailAddress available?

    Maybe something like
    strTo = ![EmailAddress]

    Also, you can test your code using the Debugger and break points. You will have to study how to use it. You can also do things like msgboxes and DEbug.Print to see what the values of your variables are as your code runs. Msgboxes not so much when you are looping through many records.

    To view strTo in the Immediate Window
    strTo = ![EmailAddress]
    Debug.print "strTo = " & strTo

    Then use the keyboard shortcut Ctrl+G to view the Immediate Window.

  5. #5
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    Or (my personal favorite), set breakpoints in appropriate places and step through the code. While you're stepping through it, you can ask the Immediate window questions while the code is in Break mode, like

    ?strVar (then hit Enter), [where strVar is a variable name].

  6. #6
    MattSC is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    11
    Itsme thank you for the advice!! I'm so glad to have this fixed, you just made my week! I added emailaddress into the recordset query and updated the StrTo and it works like a champ.

    ItsME and iPisors- thanks also for the advice on debugging and breakpoints, I'll use those tips in the future.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    ipisors is spot on with step debugging. It takes a little getting used to but knowing how to use the tool saves lots of time and frustration.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Add picture to my email code
    By cbrsix in forum Programming
    Replies: 3
    Last Post: 09-25-2013, 08:26 AM
  2. Replies: 3
    Last Post: 08-16-2013, 04:15 PM
  3. Add email to this piece of code
    By MelonFuel in forum Reports
    Replies: 1
    Last Post: 07-04-2012, 01:34 PM
  4. smtp email code
    By alyon in forum Access
    Replies: 2
    Last Post: 05-15-2012, 07:42 AM
  5. send email to email addresses in database?
    By cnstarz in forum Access
    Replies: 5
    Last Post: 03-02-2011, 09:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums