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.

Solved: How to send email from Excel

Discussion in 'Business Applications' started by thorin33, Mar 30, 2009.

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

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    In the attached xls file, the user would have this file open and would populate all the fields that are marked "User". When a date is entered into Column C, the Status (Column D) changes to "Resolved" and Send Email? (Column D) changes to "Yes".

    Here is where I get confused looking at some example vba to send a selection from the worksheet to a specified email address in the same worksheet.

    I would like to send the following to the email address in that row:

    "Your issue {row A#} regarding UWI {row G#} has come off confidential."

    When the email is sent, the Email Status (Column F) changes to Sent. Only rows with a null email status will be processed. This will prevent multuple emails from being sent.

    Hope this makes sense.

    Mike

    PS - All data is just sample data.
     

    Attached Files:

  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Sample data is sample, not real, cool. But is the data structure how you have it? That is the important part.

    How do you want to fire this event? Shall it be a button? Or by some other means?
     
  3. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    Thanks for the quick reply.

    The data structure is how I have it.

    As to how to fire the event, a button would work.

    Thanks,
    Mike

    PS - can you please add comments to the code?
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Insert a new module into your project
    1. Open the VBE (Alt + F11)
    2. Open Project Explorer (Ctrl + R)
    3. Double click your project
    4. Click Insert | Module
    5. Paste the code below into the code pane
    Code:
    Option Explicit
    
    '// Set row & column constants
    Const iColCTELNo    As Long = 1
    Const iRowStart     As Long = 3
    Const iColUWI       As Long = 7
    Const iColEmailTo   As Long = 10
    
    Sub SendEmail(ByRef rngEmail As Range, ByRef appOL As Object)
        
        '// Dimension variables
        Dim OLmail As Outlook.MailItem, ws As Worksheet
        Dim sCTEL As String, sUWI As String
        
        '// Check if there is anything in column F, Email Status column
        '// If there is anything there, exit the sub routine
        If Len(rngEmail.Value) <> 0 Then Exit Sub
        
        '// Set a variable (ws) to the same worksheet of variable passing, for ease of use
        Set ws = Workbooks(rngEmail.Parent.Parent.Name).Worksheets(rngEmail.Parent.Name)
        
        '// Create a new Outlook mail item
        Set OLmail = appOL.CreateItem(0)
        
        '// Set a couple of variables to use in the message body
        sCTEL = ws.Cells(rngEmail.Row, iColCTELNo).Value
        sUWI = ws.Cells(rngEmail.Row, iColUWI).Value
        
        '// Set the recipient of the email
        OLmail.To = ws.Cells(rngEmail.Row, iColEmailTo).Value
        
        '// Set the subject of the email
        OLmail.Subject = "Enter subject here"
        
        '// Adjust the body of the email
        OLmail.Body = "Your issue " & sCTEL & " regarding UWI " & sUWI & " has come off confidential."
        
        '// Display the email message
        OLmail.Display
        
        '// This code is only temporary for testing, it will DESTROY the emails w/o sending!
        '// Uncomment to DESTROY emails directly after creation!
    '    OLmail.Close 1
        
    End Sub
    
    Sub CheckRangeForEmailing()
    
        '// Dimension variables
        Dim OL As Object, blnOLopen As Boolean
        Dim c As Range, iLastRow As Long, strRange As String
        
        '// Set the Outlook [object] to a variable
        '// First we'll check if it is open, which the GetObject() will
        '// set if there is an instance open.  If it is not open, the
        '// variable will be 'Nothing'.
        On Error Resume Next
        Set OL = GetObject(, "Outlook.Application")
        blnOLopen = True
        If OL Is Nothing Then
            '// If the variable came back as 'Nothing' we know Outlook
            '// is not open, so we'll instead turn around and use the
            '// CreateObject() to create a new instance of the application.
            '// The boolean variable is so we can check after the routine,
            '// and if we had to create the application instance, we can
            '// close it out and clean up after ourselves.
            Set OL = CreateObject("Outlook.Application")
            blnOLopen = False
        End If
        
        With ThisWorkbook.Sheets("Master")
            
            '// Set variables to get the entire range of data to loop through.
            '// This gives the ability to do all at once, instead of individually.
            iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            strRange = "F" & iRowStart & ":F" & iLastRow
            
            '// Initiate loop
            For Each c In .Range(strRange)
            
                '// Call routine to send mail on cell (row)
                '// Advantages are that one Outlook instance has already been created,
                '// so we're not creating a new one in each loop instance, and we're
                '// keeping the email in it's own routine, keeping it clean/separated
                '// from the other code.
                Call SendEmail(c, OL)
                
                '// Change value in worksheet to 'Sent' status
                c.Value = "Sent"
                
            Next c
        End With
        
        '// If Outlook was not open when we started, close it and clean up.
        If blnOLopen = False Then OL.Quit
        
        '// Turn events back on
        Call ToggleEvents(True)
        
    End Sub
    
    Public Sub ToggleEvents(blnState As Boolean)
    '// Originally written by Zack Barresse
    '// Purpose is to turn off certain events and
    '// speed up code execution with a single call
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
    Please save a copy prior to running. Let us know how it works. This does NOT automatically send out the emails, but it displays them. Take note that if you have hundreds of lines you're running this on, you will probably crash your machine, as hundreds of emails will pop up. You can send these automatically if you want, but that's another process. If you want to incorporate that, just let us know.

    HTH
     
  5. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    Wow...thanks for the quick reply. As this list will get very large, can these be sent automatically?
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    In a word, yes. You need another program to do it though. A program I'd recommend is ClickYes. You can also use Outlook Redemption.

    Also, I made a mistake in my code. This line...
    Code:
        Dim OLmail As Outlook.MailItem, ws As Worksheet
    ... should instead be...
    Code:
        Dim OLmail As Object, ws As Worksheet
    Just change the variable type. I was doing some testing and switching between early and late binding. Apologies for the oversight.

    If you would like to use one of the programs, let us know. Using ClickYes only requires you downloading/installing and adding a couple of lines of code.
     
  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Hi Zack,

    wouldn't this code send emails for every cell in the range and not only for those whose status is not sent?:
    Code:
            For Each c In .Range(strRange)
            
                '// Call routine to send mail on cell (row)
                '// Advantages are that one Outlook instance has already been created,
                '// so we're not creating a new one in each loop instance, and we're
                '// keeping the email in it's own routine, keeping it clean/separated
                '// from the other code.
                Call SendEmail(c, OL)
                
                '// Change value in worksheet to 'Sent' status
                c.Value = "Sent"
                
            Next c
    
     
  8. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    Thanks Aj_old. I have just done some testing and it is creating an email for every cell in range.

    I will download ClickYes and would appreciate the code to send the email automatically.

    Thanks,
    Mike
     
  9. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    My apologies. After stepping through the code and running the script again, I am mistaken. It all works in creating the email. I would like to know how to automatically send the emails with ClickYes.

    Thanks again Zack.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    @Aj: Not sure what you mean. The code I've posted does not send automatically.

    @thorin: This adjusted code should work if you have Express ClickYes installed. The path assumes an XP machine, so change if neccessary...
    Code:
    Option Explicit
    
    '// Set row & column constants
    Const iColCTELNo    As Long = 1
    Const iRowStart     As Long = 3
    Const iColUWI       As Long = 7
    Const iColEmailTo   As Long = 10
    
    Sub SendEmail(ByRef rngEmail As Range, ByRef appOL As Object)
        
        '// Dimension variables
        Dim OLmail As Object, ws As Worksheet
        Dim sCTEL As String, sUWI As String
    
        
        '// Check if there is anything in column F, Email Status column
        '// If there is anything there, exit the sub routine
        If Len(rngEmail.Value) <> 0 Then Exit Sub
        
        '// Set a variable (ws) to the same worksheet of variable passing, for ease of use
        Set ws = Workbooks(rngEmail.Parent.Parent.Name).Worksheets(rngEmail.Parent.Name)
        
        '// Create a new Outlook mail item
        Set OLmail = appOL.CreateItem(0)
        
        '// Set a couple of variables to use in the message body
        sCTEL = ws.Cells(rngEmail.Row, iColCTELNo).Value
        sUWI = ws.Cells(rngEmail.Row, iColUWI).Value
        
        '// Set the recipient of the email
        OLmail.To = ws.Cells(rngEmail.Row, iColEmailTo).Value
        
        '// Set the subject of the email
        OLmail.Subject = "Enter subject here"
        
        '// Adjust the body of the email
        OLmail.Body = "Your issue " & sCTEL & " regarding UWI " & sUWI & " has come off confidential."
        
        '// Display the email message
        OLmail.Display
        
        '// This code is only temporary for testing, it will DESTROY the emails w/o sending!
        '// Uncomment to DESTROY emails directly after creation!
    '    OLmail.Close 1
        
    End Sub
    
    Sub CheckRangeForEmailing()
    
        '// Dimension variables
        Dim objwShell As Object
        Dim OL As Object, blnOLopen As Boolean
        Dim c As Range, iLastRow As Long, strRange As String
        
        '// Set the Outlook [object] to a variable
        '// First we'll check if it is open, which the GetObject() will
        '// set if there is an instance open.  If it is not open, the
        '// variable will be 'Nothing'.
        On Error Resume Next
        Set OL = GetObject(, "Outlook.Application")
        blnOLopen = True
        If OL Is Nothing Then
            '// If the variable came back as 'Nothing' we know Outlook
            '// is not open, so we'll instead turn around and use the
            '// CreateObject() to create a new instance of the application.
            '// The boolean variable is so we can check after the routine,
            '// and if we had to create the application instance, we can
            '// close it out and clean up after ourselves.
            Set OL = CreateObject("Outlook.Application")
            blnOLopen = False
        End If
        
        'Activate ClickYes
        Set objwShell = CreateObject("wscript.shell")
        objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")
    
        With ThisWorkbook.Sheets("Master")
            
            '// Set variables to get the entire range of data to loop through.
            '// This gives the ability to do all at once, instead of individually.
            iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            strRange = "F" & iRowStart & ":F" & iLastRow
            
            '// Initiate loop
            For Each c In .Range(strRange)
            
                '// Call routine to send mail on cell (row)
                '// Advantages are that one Outlook instance has already been created,
                '// so we're not creating a new one in each loop instance, and we're
                '// keeping the email in it's own routine, keeping it clean/separated
                '// from the other code.
                Call SendEmail(c, OL)
                
                '// Change value in worksheet to 'Sent' status
                c.Value = "Sent"
                
            Next c
        End With
        
        'Stop Clickyes
        objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop")
    
        '// If Outlook was not open when we started, close it and clean up.
        If blnOLopen = False Then OL.Quit
        
        '// Turn events back on
        Call ToggleEvents(True)
        
    End Sub
    
    Public Sub ToggleEvents(blnState As Boolean)
    '// Originally written by Zack Barresse
    '// Purpose is to turn off certain events and
    '// speed up code execution with a single call
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
    HTH
     
  11. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    Zach,

    Thanks for your help so far. The one thing that I don't see is the check on column E (send Email?). I would only like to send emails if a closed date is entered and a previous email has not been sent.

    I installed ClickYes and entered in your new code. The emails are created but not sent automatically. What am I doing wrong?
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I didn't put a check on column E. To do that, add this code...

    Code:
        If Len(rngEmail.Value) <> 0 Then Exit Sub
    'Add this line here at this location....
        If rngEmail.Offset(0, -1).Value <> "Yes" Then Exit Sub
    Is ClickYes running? You should see it in the system tray.
     
  13. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    ClickYes is running in the system tray. It still does not send the email automatically.
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    And you checked the path of both command lines for ClickYes in the code?

    Oh, wait, it's still has the .Display command, doesn't it.. change it to .Send instead. Duh. (The duh is for me. :) )
     
  15. thorin33

    thorin33 Thread Starter

    Joined:
    Mar 24, 2009
    Messages:
    11
    Almost there. I am running some testing on different scenarios. The only thing that I am noticing is that the email status column changes to "Sent" even if an email was not sent.

    Thanks so much for all your work!!
     
  16. 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/814146

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice