Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Automatic Email from Excel List


(!)

NoviceCoder's Avatar
NoviceCoder NoviceCoder is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Oct 2010
Experience: Beginner
07-Oct-2010, 06:49 PM #1
Automatic Email from Excel List
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
File Type: xls Email Test File.xls (30.5 KB, 896 views)
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
09-Oct-2010, 10:05 AM #2
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-a...el-2003-a.html

Jimmy
NoviceCoder's Avatar
NoviceCoder NoviceCoder is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Oct 2010
Experience: Beginner
11-Oct-2010, 04:02 PM #3
Thank you, Jimmy. I will try this and let you know if it works.
NoviceCoder's Avatar
NoviceCoder NoviceCoder is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Oct 2010
Experience: Beginner
11-Oct-2010, 06:43 PM #4
I just copied and pasted the code into a blank macro, and it worked perfectly! Thank you so much, Jimmy!!
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
12-Oct-2010, 01:43 AM #5
You are welcome. Convey my greetings to Susan!
NoviceCoder's Avatar
NoviceCoder NoviceCoder is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Oct 2010
Experience: Beginner
12-Oct-2010, 02:07 PM #6
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!
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
13-Oct-2010, 03:54 AM #7
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
shaship13's Avatar
shaship13 shaship13 is offline
Junior Member with 2 posts.
 
Join Date: Oct 2010
Experience: Computer Illiterate
13-Oct-2010, 12:40 PM #8
Hi Jimmy

I am unable to download the file, can your email me at shaship13@rediffmail.com, it will be a great help.

Regards
Shashi
shaship13's Avatar
shaship13 shaship13 is offline
Junior Member with 2 posts.
 
Join Date: Oct 2010
Experience: Computer Illiterate
13-Oct-2010, 12:41 PM #9
Hi
I am unable to download the file, can your email me at shaship13@rediffmail.com, it will be a great help.

Regards
Shashi
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
13-Oct-2010, 01:40 PM #10
Quote:
Originally Posted by shaship13 View Post
Hi
I am unable to download the file, can your email me at shaship13@rediffmail.com, it will be a great help.
Perhaps, if you give me a bit of background. Above all else, I'm very curious what file you are referring to.

Jimmy

Last edited by Jimmy the Hand; 13-Oct-2010 at 02:00 PM..
NoviceCoder's Avatar
NoviceCoder NoviceCoder is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Oct 2010
Experience: Beginner
13-Oct-2010, 02:19 PM #11
You're awesome, Jimmy! The additional code worked perfectly. Thank you so much for your help!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
automatic, email, excel, outlook

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
How to send an automatic email from excel? vasu0505 Business Applications 13 13-Oct-2010 11:56 AM
Solved: Automatic email from excel based on date chad_hunt Business Applications 24 08-Jun-2010 02:55 PM
Problem handling mass emails from outlook and/or Gmail brion dublin Web & Email 2 24-Sep-2009 01:58 PM
Automatic Email from Excel based on Date in Cell DMazz Business Applications 27 08-Sep-2009 07:24 AM
Adding to an existing outlook distribution list from excel davemuzza Business Applications 4 05-Aug-2009 09:51 AM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑