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,225
    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 offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,584
    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,225
    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