Results 1 to 5 of 5

VBA Forward Multiple Emails using Control table with Subjects

  1. #1
    shabee99 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jun 2019
    Posts
    2

    VBA Forward Multiple Emails using Control table with Subjects

    Hello,

    I am attempting to forward emails, including attachments, based on the subject line. I have multiple subjects and the related email recipients in a control table. My VBA code runs without error, however it only sends the first record's email. I confirmed the one email get sent, but I need the code to move to the next record until end of control table forwarding multiple emails to multiple recipients. My code is below:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Sub ForwardEmailswithaSpecificSubjects()
    
    
        Dim objapp As Outlook.Application
        Dim objns As Outlook.NameSpace
        Set objns = Outlook.GetNamespace("MAPI")
               
        Dim objCurrentFolder As Outlook.Folder
        Dim objVariant As Variant
        Dim objForwardMail As MailItem
        Dim i As Long
        Set Outlook.ActiveExplorer.CurrentFolder = objns.GetDefaultFolder(6)
        Set objCurrentFolder = Outlook.ActiveExplorer.CurrentFolder
        
        'Set Items = objns.GetDefaultFolder(olFolderInbox).Parent.Folders("Inbox").Items
        
        Dim myRs As DAO.Recordset
        Dim myRb As DAO.Recordset
        Set myRs = CurrentDb().OpenRecordset("Email_List_Test")
        Set myRb = CurrentDb().OpenRecordset("bodytext")
    
    
       
        For i = objCurrentFolder.Parent.Folders("Inbox").Items.Count To 1 Step -1
            If TypeOf objCurrentFolder.Items.Item(i) Is MailItem Then
               Set objVariant = objCurrentFolder.Parent.Folders("Inbox").Items.Item(i)
    
    
                    Set objForwardMail = objVariant.Forward
                             
                   'change the recipient email address as per your needs
                       'Use ; to connect several recipients
                    If myRs.RecordCount > 0 Then
                    myRs.MoveFirst
                    If objVariant.Subject = myRs![lookup_Subject] Then
                    With objForwardMail
                       .To = myRs![Email_Distribution_list]
                       .Subject = "Securemail: " + .Subject
                       .Body = myRb![bodyofemail]
                       .Send
                    myRs.MoveNext
                    End With
                    End If
                    End If
                 
                End If
            
        Next i
       
        
    End Sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,349
    What is content of [Email_Distribution_list] field? Is this a comma or semi-colon separated string of multiple addresses?

    Tested code and it works.

    Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shabee99 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jun 2019
    Posts
    2
    June - Yes, the code works. However my control table (Email_List_TEST), which has two fields (Lookup_Subject & Email_Distribution_List) has 180 records/line items looking for different subjects to forward to different recipients. I am not getting any errors, it just looks at the first record in the control table and the email gets forwarded BUT I need it to loop through the 179 other records and perform the same function (looping through the control table) for each record.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    You're not looping the recordset, which would look like:

    Code:
    Do While Not myRs.EOF
      'perform your process here
      myRs.MoveNext
    Loop
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,349
    Did not answer my first 2 questions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 6
    Last Post: 04-17-2017, 04:47 PM
  2. Grade level table and subjects table
    By Khalil Handal in forum Access
    Replies: 4
    Last Post: 12-10-2016, 05:20 AM
  3. Replies: 5
    Last Post: 12-14-2014, 05:06 AM
  4. Multiple Forms carrying forward data
    By adams.bria in forum Forms
    Replies: 5
    Last Post: 04-04-2012, 10:50 PM
  5. updating category/subjects
    By jalal in forum Access
    Replies: 2
    Last Post: 02-07-2012, 01:42 PM

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