Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Email automatic in Excel with VBA to edit

  1. #1
    Join Date
    27th July 2010
    Posts
    66

    Email automatic in Excel with VBA to edit

    My spreadsheet needs VBA code edit, to send EMAILS to rows, that are NOT BLANK in the worksheet RESULTS, excluding the HEADER.

    See sample file http://www.srands.co.uk/exoftable3.xls

    Issue1: To get VBA code working edited (See code below, 9th line, For r = 2 To 2 'Needs editing so that data in 'not blank' rows only is included. Also r = row, not to be confused with column r) to return email for only the 2nd row (Row after HEADER) entry only, on the RESULTS page. However the number of not blank rows is a variable, and will depend upon the rows that meet the criteria in 'WORKSHEET' in column U.

    Issue2: If I expand the row range to a full page upto row 51, many BLANK emails are generated (Because of blank rows, the auto-generated fields would be BLANK).
    I don't know what VBA code to use instead though.
    In formula's for the 'RESULTS' page I would use a command that checks if the row is not blank, something like =IF(AND(A2=0),"",'email command')WHAT IS THE VB EQUIVALENT OF SOMETHING LIKE THIS?
    For this spreadsheet the number of RESULTS will be unknown depending on the information/data available, hence I want to include NOT BLANK entries from rows 2 to 51.

    PURPOSE OF DESIRED SOLUTION:
    CODE NEEDS EDITING, JUST TO COUNT NOT BLANK ROWS IN THE WORKSHEET 'RESULTS':

    VB:
    Private Declare Function ShellExecute Lib "shell32.dll" _ 
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
    ByVal nShowCmd As Long) As Long 
    Sub SendEMail() 
        Dim Email As String, Subj As String 
        Dim Msg As String, URL As String 
        Dim r As Integer, x As Double 
        For r = 2 To 2 'Needs editing so that data in 'not blank' rows only is included
             '       Get the email address
            Email = Cells(r, 10) 
             
             '       Message subject
            Subj = "Your car for sale.  " & Cells(r, 1).Text & "." 
             '       Compose the message
            Msg = "" 
            Msg = Msg & "Dear " & Cells(r, 11) & "," & vbCrLf & vbCrLf 
            Msg = Msg & "I like your car, the " & Cells(r, 1).Text & "." & vbCrLf & vbCrLf 
            Msg = Msg & "Please call me back.  " 
            Msg = Msg & "It is " & Cells(r, 2).Text & "." & vbCrLf & vbCrLf 
             
            Msg = Msg & "Cheers " & vbCrLf & vbCrLf 
            Msg = Msg & "Stephan Rands" & vbCrLf 
            Msg = Msg & "07772000679" & vbCrLf 
            Msg = Msg & "[EMAIL="mail@srands.co.uk"]mail@srands.co.uk[/EMAIL]" 
             
             
             '       Replace spaces with %20 (hex)
            Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
            Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
             
             '       Replace carriage returns with %0D%0A (hex)
            Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") '       Create the URL
            URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
             '       Execute the URL (start the email client)
            ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
             '       Wait two seconds before sending keystrokes
            Application.Wait (Now + TimeValue("0:00:02")) 
            Application.SendKeys "%s" 
        Next r 
    End Sub 
    
    
    See sample file http://www.srands.co.uk/exoftable3.xls

    TheWORKSHEET 'RESULTS', has the VISUAL BASIC code called 'Send EMail'. Obviously to view 'RESULTS' worksheet VISUAL BASIC code, View, Tools Bars, Visual Basic, then on the Toolbar press the play symbol (R/H arrow), Step into.

    Or to play the MACRO of the rows that meet all criteria in 'WORKSHEET', shown in 'RESULTS', View, Tools Bars, Visual Basic, then on the Toolbar press the play symbol (R/H arrow), Run.

    Cheers

    Stephan

    Cross threads:
    http://www.excelforum.com/excel-prog...html?p=2762981
    http://www.mrexcel.com/forum/showthr...18#post3122118

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    27th July 2010
    Posts
    66

    Re: Email automatic in Excel with VBA to edit

    I've solved it!

    See my WORKING file http://www.srands.co.uk/exoftable3.xls

    Simple 1 line code change:
    VB:
    For R = 2 To Range("m4") + 1 
    
    
    So the full VBA code for the RESULTS page is:
    VB:
    Private Declare Function ShellExecute Lib "shell32.dll" _ 
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
    ByVal nShowCmd As Long) As Long 
    Sub SendEMail() 
        Dim Email As String, Subj As String 
        Dim Msg As String, URL As String 
        Dim R As Integer, x As Double 
        For R = 2 To Range("m4") + 1 
             '       Get the email address
            Email = Cells(R, 10) 
             
             '       Message subject
            Subj = "Your car for sale.  " & Cells(R, 1).Text & "." 
             '       Compose the message
            Msg = "" 
            Msg = Msg & "Dear " & Cells(R, 11) & "," & vbCrLf & vbCrLf 
            Msg = Msg & "I like your car, the " & Cells(R, 1).Text & "." & vbCrLf & vbCrLf 
            Msg = Msg & "Please call me back.  " 
            Msg = Msg & "It is " & Cells(R, 2).Text & "." & vbCrLf & vbCrLf 
             
            Msg = Msg & "Cheers " & vbCrLf & vbCrLf 
            Msg = Msg & "Stephan Rands" & vbCrLf 
            Msg = Msg & "07772000679" & vbCrLf 
            Msg = Msg & "[EMAIL="mail@srands.co.uk"]mail@srands.co.uk[/EMAIL]" 
             
             
             '       Replace spaces with %20 (hex)
            Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
            Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
             
             '       Replace carriage returns with %0D%0A (hex)
            Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") '       Create the URL
            URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
             '       Execute the URL (start the email client)
            ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
             '       Wait two seconds before sending keystrokes
            Application.Wait (Now + TimeValue("0:00:02")) 
            Application.SendKeys "%s" 
        Next R 
    End Sub 
    
    
    NOTE: This code is of an edit from J Walk http://spreadsheetpage.com/index.php...il_from_excel/

    See my WORKING file http://www.srands.co.uk/exoftable3.xls

    This is a great working file, I wonder if it is possible to do the same with an EXCEL 2003 FORMULA? (Aka not Ron de Bruin or erlandsendata!)

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Send an automatic email from excel every x minutes
    By crazytimechris in forum Excel and/or Email Help
    Replies: 1
    Last Post: January 23rd, 2012, 03:39
  2. Automatic email - act as a remainder
    By suahm in forum Excel and/or Email Help
    Replies: 2
    Last Post: October 25th, 2005, 21:15
  3. Automatic Email when conditions
    By billyj in forum Excel and/or Email Help
    Replies: 2
    Last Post: August 20th, 2004, 23:20
  4. Automatic Email when conditions
    By billyj in forum EXCEL HELP
    Replies: 2
    Last Post: August 20th, 2004, 23:20
  5. Automatic Email
    By ADE in forum Excel and/or Email Help
    Replies: 7
    Last Post: May 26th, 2004, 16:58

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
  •  
porno