A Simple Send Email Procedure That Works

sdruley

sdruley

Well-known Member
Joined
Oct 3, 2010
Messages
557
Office Version
  1. 365
Platform
  1. Windows
Apparently, the technology for sending emails from Excel vba is in a state of flux, particularly, when you consider that the syntax seems to be different for each version of Excel or each version of your operating system.
Does anyone know of a simple approach for Excel 360, 64 bit and windows 10?
My goal is to take the contents of one cell on a worksheet, assign a date and time stamp and email it to my brother

Date _____Time _______ T
3/4/2019, 12:32:02 AM, -1 with no quotes

I am in to my 3rd day on this problem. Anyone's assistance would be greatly appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Howdy

I dealt with a similar problem for another user earlier today.

I'm not clear as to how your data is laid out, so am going to assume Date and Time are in two different cells.


  1. Is "-1 with no quotes" part of the info to go in the email?
  2. Could you please provide a simple layout of the table holding your data (with grid ref's) showing where the following are located:
    • Recipient's email address
    • Date
    • Time
    • Other
  3. What goes in the following "fields" of the email:
    • Subject?
    • Message body?
  4. Where in the email do you want the date and time to be placed?
 
Upvote 0
Col Delane said:
Howdy

I dealt with a similar problem for another user earlier today.

I'm not clear as to how your data is laid out, so am going to assume Date and Time are in two different cells.


  1. Is "-1 with no quotes" part of the info to go in the email?
  2. Could you please provide a simple layout of the table holding your data (with grid ref's) showing where the following are located:
    • Recipient's email address
    • Date
    • Time
    • Other
  3. What goes in the following "fields" of the email:
    • Subject?
    • Message body?
  4. Where in the email do you want the date and time to be placed?
Click to expand...

SEND TO: Const = xxx
SENDER: Const = yyy
COPIED: Const = zzz
SUBJECT: Rainbow 2
BODY: 3/4/2019, 6:30:04 AM, -1 {Now()}, {Now()}, {-1,1,9}
REFERENCE/PLACEMENT: New Sheet @ $A$1, {-1,1,9} = $A$6

Would try sending to yourself to see if code works
 
Upvote 0
Col Delane,

Forgot to thank you for your efforts to work on this. I am very grateful.
 
Upvote 0
I was trying to build a non-VBA solution using just the HYPERLINK function but can't quite get the message body to work properly so am adapting some old code I had in my Excel toolbox that sends an email via Outlook.

A couple more clarifications:
1. Are the Date and Time values required the current (i.e. Now ) values of each at the time the email is sent?
2. I'm not familiar with the following elements of the BODY - could you please explain? (i.e. should this text be sent as is, or are they formula arguments that should return values?)
-1 {Now()}, {Now()}, {-1,1,9}​
REPLACEMENT New Sheet @ $A$1, {-1,1,9} = $A$6
​
 
Upvote 0
Col Delane said:
I was trying to build a non-VBA solution using just the HYPERLINK function but can't quite get the message body to work properly so am adapting some old code I had in my Excel toolbox that sends an email via Outlook.

A couple more clarifications:
1. Are the Date and Time values required the current (i.e. Now ) values of each at the time the email is sent?
2. I'm not familiar with the following elements of the BODY - could you please explain? (i.e. should this text be sent as is, or are they formula arguments that should return values?)
-1 {Now()}, {Now()}, {-1,1,9}​
REPLACEMENT New Sheet @ $A$1, {-1,1,9} = $A$6
​
Click to expand...

{Now()} refers to the vba code formatted as the date and then the time
{-1,1,9} refers to the possible solutions in the set
$A$6 is a typo
 
Upvote 0
Try this:

I'm still not completely clear about the content of the Message Body - but you should be able to modify those two lines/cells as you desire.

Spreadsheet:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]
​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Email Field
Current Data​
Cell Content​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Send To:fastlane@tpg.com.au<< Data
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Sender:As per mail client<< Data
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
Copy To:col.delane@tpg.com.au<< Data
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
Subject line text:Rainbow 2<< Data
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
Message body text:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
 Line 1:03/07/19, 07:16:12 PM<< Formula
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
 Line 2:Reference/Placement: New Sheet @ $A$1, {-1,1,9}<< Data
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

Formulas:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]
​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]
​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]
​
 Line 1:= TEXT( NOW(), "mm/dd/yy, hh:mm:ss AM/PM" )<< Formula
I'm from down-under (Australia), so just check that NOW() and the TEXT formula returns the correct value and format for the date for your region.

Defined Names:
BodyLine1='.'!$B$7
BodyLine2='.'!$B$8
CopyToEmailAddress='.'!$B$4
SendToEmailAddress='.'!$B$2
Subject='.'!$B$5

VBA Code:
  1. Copy this code to a standard module in the Visual Basic Editor
  2. Add a button (Form Control) to the spreadsheet, or to your Quick Access Toolbar, and link it to the macro.
Code: 
Option Explicit

Sub mail_text_html()

    Dim OutApp As Object
    Dim OutMail As Object
    
    Dim strbody As String
    
    Dim EmailSendTo As String
    Dim EmailCopyTo As String
    Dim EmailSubject As String
    Dim myRange As Range
    Dim EmailBody1 As String
    Dim EmailBody2 As String
    
    Set myRange = Range("SendToEmailAddress")
    EmailSendTo = myRange.Text
    
    Set myRange = Range("CopyToEmailAddress")
    EmailCopyTo = myRange.Text
    
    Set myRange = Range("Subject")
    EmailSubject = myRange.Text
    
    Set myRange = Range("BodyLine1")
    EmailBody1 = myRange.Text
    
    Set myRange = Range("BodyLine2")
    EmailBody2 = myRange.Text


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
    With OutMail
        .Subject = EmailSubject
        .To = EmailSendTo
        .CC = EmailCopyTo
        .Body = EmailBody1 & vbNewLine & EmailBody2
        
        Select Case MsgBox("Click:" & vbLf & vbLf & "Yes to DISPLAY the message (then manually send from email client)," & vbLf & vbLf & _
            "No to SEND the message directly without displaying, or" & vbLf & vbLf & "Cancel to abort.", vbYesNoCancel Or vbQuestion Or vbDefaultButton1, "E-mail Despatch")
            
            Case vbYes
                    .Display
            Case vbNo
                    .Send
                    MsgBox "Done."
            Case vbCancel
                    Exit Sub
                    
        End Select
                
        End With

On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub


Post back with any issues / what you think.
 
Upvote 0
Col Delane said:
Try this:

I'm still not completely clear about the content of the Message Body - but you should be able to modify those two lines/cells as you desire.

Spreadsheet:
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]A[/COLOR]
​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]1[/COLOR]​
Email Field
Current Data​
Cell Content​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]2[/COLOR]​
Send To:fastlane@tpg.com.au<< Data
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]3[/COLOR]​
Sender:As per mail client<< Data
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]4[/COLOR]​
Copy To:col.delane@tpg.com.au<< Data
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]5[/COLOR]​
Subject line text:Rainbow 2<< Data
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]6[/COLOR]​
Message body text:
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]7[/COLOR]​
 Line 1:03/07/19, 07:16:12 PM<< Formula
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]8[/COLOR]​
 Line 2:Reference/Placement: New Sheet @ $A$1, {-1,1,9}<< Data
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]9[/COLOR]​

Formulas:
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]B[/COLOR]
​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]C[/COLOR]
​
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]7[/COLOR]
​
 Line 1:= TEXT( NOW(), "mm/dd/yy, hh:mm:ss AM/PM" )<< Formula
I'm from down-under (Australia), so just check that NOW() and the TEXT formula returns the correct value and format for the date for your region.

Defined Names:
BodyLine1='.'!$B$7
BodyLine2='.'!$B$8
CopyToEmailAddress='.'!$B$4
SendToEmailAddress='.'!$B$2
Subject='.'!$B$5

VBA Code:
  1. Copy this code to a standard module in the Visual Basic Editor
  2. Add a button (Form Control) to the spreadsheet, or to your Quick Access Toolbar, and link it to the macro.
Code: 
Option Explicit

Sub mail_text_html()

    Dim OutApp As Object
    Dim OutMail As Object
    
    Dim strbody As String
    
    Dim EmailSendTo As String
    Dim EmailCopyTo As String
    Dim EmailSubject As String
    Dim myRange As Range
    Dim EmailBody1 As String
    Dim EmailBody2 As String
    
    Set myRange = Range("SendToEmailAddress")
    EmailSendTo = myRange.Text
    
    Set myRange = Range("CopyToEmailAddress")
    EmailCopyTo = myRange.Text
    
    Set myRange = Range("Subject")
    EmailSubject = myRange.Text
    
    Set myRange = Range("BodyLine1")
    EmailBody1 = myRange.Text
    
    Set myRange = Range("BodyLine2")
    EmailBody2 = myRange.Text


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
    With OutMail
        .Subject = EmailSubject
        .To = EmailSendTo
        .CC = EmailCopyTo
        .Body = EmailBody1 & vbNewLine & EmailBody2
        
        Select Case MsgBox("Click:" & vbLf & vbLf & "Yes to DISPLAY the message (then manually send from email client)," & vbLf & vbLf & _
            "No to SEND the message directly without displaying, or" & vbLf & vbLf & "Cancel to abort.", vbYesNoCancel Or vbQuestion Or vbDefaultButton1, "E-mail Despatch")
            
            Case vbYes
                    .Display
            Case vbNo
                    .Send
                    MsgBox "Done."
            Case vbCancel
                    Exit Sub
                    
        End Select
                
        End With

On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub


Post back with any issues / what you think.
Click to expand...

This is a masterpiece of instruction aimed at the worksheet, range names, formulas, error algorithms and vba code that brings all of the intellectual assets together into one solution. It delivers world class results. My compliments to an incredible, successful effort in the midst of little to no technical guidance from Microsoft. WOW!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Mail merge to insert the picture in the mail body and schedule the email
Replies
0
Views
769
rafeek4u
R
S
  • Question
Send automated emails based on dates
Replies
3
Views
717
somesoldiers
S
spacecaptainsuperguy
  • Solved
Simple VBA for Outlook - Copy last sent email
Replies
6
Views
1K
spacecaptainsuperguy
spacecaptainsuperguy
O
  • Question
Excel/VBA to conditionally send emails per schedule
Replies
0
Views
698
OC2PS
O
A
  • Question
Excel macro to send email with attachment
Replies
2
Views
587
atuljadhavnetafim
A

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top