1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Automatic Email from Excel List

Discussion in 'Business Applications' started by NoviceCoder, Oct 7, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. NoviceCoder

    NoviceCoder Thread Starter

    Joined:
    Oct 7, 2010
    Messages:
    5
    Hi,

    I am trying to help a co-worker automate her email notification to employees who have not followed correct procedures on their expense reports. She has an Excel spreadsheet with the name and email address for each employee, along with 10 columns to represent the type of violation. I have attached a sample of the spreadsheet, where an 'X' indicates the nature of the problem. She would like to be able to run a macro to automatically send an email to each person with an 'X' in any of the columns. In the text of the email, it would indicate the month (from column C) and the type of problem based on the column header.

    For example, based on the attached sample file, John Smith would receive an email as follows:

    Hello,



    Your September expense report was submitted with the following errors:
    • Blank GL Codes
    • Missing or Incomplete Description
    Please correct the errors and resubmit this expense report.

    Thank you,
    Susan Larson

    I'm an accountant who has done some automation using macros in Excel and Access, but I'm not sure which application would be best for this situation. If it would be easier, I can set this up in Access, but I would prefer Excel since my co-worker has more experience with spreadsheets than databases.

    I found several other threads explaining how to send an automatic email from Excel, but I haven't found one for sending an individualized email to a list of recipients. If this question has been answered, please feel free to redirect me. Otherwise, I'd appreciate any suggestions.
    Thanks!

    OS and Software:
    Windows XP
    Outlook 2007
    Excel 2007
     

    Attached Files:

  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Try this code:

    Code:
    Sub Notify()
        Dim WS As Worksheet, Rng As Range, c As Range
        Dim OutApp As Object, OutMail As Object
        Dim Msg As String, Addr As String, FName As String, i As Long
        
        Set OutApp = CreateObject("Outlook.Application")
        Set WS = ThisWorkbook.Sheets("Sheet1")
        Set Rng = WS.Range("A2", WS.Range("A" & Rows.Count).End(xlUp))
        For Each c In Rng
            FName = Mid(Trim(c), InStr(Trim(c), " ") + 1)
            Msg = "Hello " & UCase(Left(FName, 1)) & LCase(Mid(FName, 2)) & "," & Chr(13) & Chr(13) & Chr(13)
            Msg = Msg & "Your " & c.Offset(, 2) & " expense report was submitted with the following errors:" & Chr(13) & Chr(13)
            For i = 4 To 13
                If WS.Cells(c.Row, i) = "X" Then
                    Msg = Msg & "   -" & WS.Cells(1, i) & Chr(13)
                End If
            Next
            Msg = Msg & Chr(13) & "Please correct the errors and resubmit this expense report." & Chr(13)
            Msg = Msg & Chr(13) & "Thank you,"
            Msg = Msg & Chr(13) & "Susan Larson"
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
            .to = c.Offset(, 1)
            .CC = ""
            .BCC = ""
            .Subject = "Incomplete expense report"
            .Body = Msg
            .Send
            End With
            Set OutMail = Nothing
        Next
    End Sub
    I couldn't test it, because I have no Outlook installed. But I trust it will work.
    Outlook handling code was taken from this thread:
    http://forums.techguy.org/business-applications/950444-excel-2003-a.html

    Jimmy
     
  3. NoviceCoder

    NoviceCoder Thread Starter

    Joined:
    Oct 7, 2010
    Messages:
    5
    Thank you, Jimmy. I will try this and let you know if it works.
     
  4. NoviceCoder

    NoviceCoder Thread Starter

    Joined:
    Oct 7, 2010
    Messages:
    5
    I just copied and pasted the code into a blank macro, and it worked perfectly! Thank you so much, Jimmy!!
     
  5. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    You are welcome. Convey my greetings to Susan! ;)
     
  6. NoviceCoder

    NoviceCoder Thread Starter

    Joined:
    Oct 7, 2010
    Messages:
    5
    Hi,
    This code from Jimmy works great. I just realized, though, that there will be another scenario that is not covered by the macro. If there is a name on the list with no problems indicated (no X in any of the columns), we don't want to send an email to that person. Is there something I can add to the code so that it will only send an email if there are columns marked with an X?
    Thank you!
     
  7. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Hi

    It takes only a minor modification of the code.
    Code:
    Sub Notify()
        Dim WS As Worksheet, Rng As Range, c As Range
        Dim OutApp As Object, OutMail As Object
        Dim Msg As String, Addr As String, FName As String, i As Long
        Dim NotificationNecessary As Boolean
        
        Set OutApp = CreateObject("Outlook.Application")
        Set WS = ThisWorkbook.Sheets("Sheet1")
        Set Rng = WS.Range("A2", WS.Range("A" & Rows.Count).End(xlUp))
        For Each c In Rng
            NotificationNecessary = False
            FName = Mid(Trim(c), InStr(Trim(c), " ") + 1)
            Msg = "Hello " & UCase(Left(FName, 1)) & LCase(Mid(FName, 2)) & "," & Chr(13) & Chr(13) & Chr(13)
            Msg = Msg & "Your " & c.Offset(, 2) & " expense report was submitted with the following errors:" & Chr(13) & Chr(13)
            For i = 4 To 13
                If WS.Cells(c.Row, i) = "X" Then
                    Msg = Msg & "   -" & WS.Cells(1, i) & Chr(13)
                    NotificationNecessary = True
                End If
            Next
            If NotificationNecessary Then
                Msg = Msg & Chr(13) & "Please correct the errors and resubmit this expense report." & Chr(13)
                Msg = Msg & Chr(13) & "Thank you,"
                Msg = Msg & Chr(13) & "Susan Larson"
                
                Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
                With OutMail
                .to = c.Offset(, 1)
                .CC = ""
                .BCC = ""
                .Subject = "Incomplete expense report"
                .Body = Msg
                .Send
                End With
                Set OutMail = Nothing
            End If
        Next
    End Sub
    Hope it works.

    Jimmy
     
  8. shaship13

    shaship13

    Joined:
    Oct 13, 2010
    Messages:
    2
  9. shaship13

    shaship13

    Joined:
    Oct 13, 2010
    Messages:
    2
  10. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
  11. NoviceCoder

    NoviceCoder Thread Starter

    Joined:
    Oct 7, 2010
    Messages:
    5
    You're awesome, Jimmy! The additional code worked perfectly. Thank you so much for your help!
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/954888