Results 1 to 23 of 23

EXCEL VBA send dynamic emails messages based on trigger column

Threaded View

  1. #1
    Registered User
    Join Date
    04-17-2016
    Location
    St Louis
    MS-Off Ver
    2013
    Posts
    13

    EXCEL VBA send dynamic emails messages based on trigger column

    I am attempting to send emails to a distribution list based on a trigger column (column E). Each row that contains the word "Captured" should send an email to the address in column A. The email body for these outgoing emails should contain content in column D. Ideally I would like the Name (B) and Client (C) to dynamically appear in the Subject of the email.


    The code below, fires to the correct recipients but sends the same message body (last one within the list to all email addresses).

    I am in need of this for a large project so any help would be amazing!
    Dim outlookapp As Object
    Dim Outlookmailitem As Object
    Dim Icounter As Integer
    Dim maildest As String
    Dim mess As String
    
    Set outlookapp = CreateObject("outlook.application")
    Set Outlookmailitem = outlookapp.CreateItem(0)
    
    With Outlookmailitem
    maildest = ""
    For Icounter = 1 To WorksheetFunction.CountA(Columns(1))
    If maildest = "" And Cells(Icounter, 1).Offset(0, 4) = "Captured" Then
    mess = Cells(Icounter, 4).Value
    maildest = Cells(Icounter, 1).Value
    
    ElseIf maildest <> "" And Cells(Icounter, 1).Offset(0, 4) = "Captured" Then
    mess = Cells(Icounter, 4).Value
    maildest = maildest & ";" & Cells(Icounter, 1).Value
    
    End If
    Next Icounter
    
    .CC = maildest
    .Subject = "name and Client"
    .Body = mess
    .Send
    End With
    
    Set Outlookmailitem = Nothing
    Set outlookapp = Nothing
    End Sub
    Last edited by mcjonesy; 04-17-2016 at 09:41 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel vba to auto-send customer emails (duplicate emails issue)
    By nadz84 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2015, 10:08 AM
  2. Replies: 2
    Last Post: 12-19-2014, 11:28 AM
  3. Send Emails once expiry date is reached, and generate report based on emails sent
    By demonicscorpion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2014, 05:36 AM
  4. [SOLVED] Need help getting excel to send automatic emails based on cell value
    By Quito in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-11-2014, 07:09 AM
  5. Automatically send an email based on the value of a cell and/or trigger date
    By psn.arya in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 06:17 PM
  6. Send Emails based on cell value
    By kaki1956 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2010, 08:28 PM
  7. Sending macro emails using excel: Send emails with their passwords.
    By loveisblind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2009, 03:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1