send email from excel multiple row containing today's date

G

getuday

New Member
Joined
Nov 30, 2010
Messages
23
Hi All,


I have an excel file which I use everyday, it contains person's name, date of when to do the report and email ID. What i want is, the excel file should send mail as per the due date.
I have pasted the file below.
In this you can see the column "Report Date" contains today's date (30th Nov). So if today's date matches with the 30th Nov then it should send mail to the concerned person (column D) the name of report (Column B) along with Report date (Column D).
Is it possible?

Also please tell me is there anyway i can do this without opening the excel file.. for eg. creating an batch file.

<TABLE style="WIDTH: 466pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=620 border=0><COLGROUP><COL style="WIDTH: 32pt" width=43><COL style="WIDTH: 218pt" width=291><COL style="WIDTH: 83pt" span=2 width=110><COL style="WIDTH: 50pt" width=66></COLGROUP><TBODY><TR style="HEIGHT: 41.25pt" height=55><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 32pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 41.25pt; BACKGROUND-COLOR: black">Sr No</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black">Name of the Report</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black">Report Responsibility</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black">Email Id</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black">Report Date (From)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">1</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Acc - Weekly</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Radha Diwali</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">uday.naik@orange-ftgroup.com</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40512">30-Nov-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">2</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Acc Sales Channel</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ajeeb Surila</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ajeeb.Surila@gmail.com</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40513">01-Dec-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">3</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">All Orders - Daily</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Sam Parmar</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Sam.Parmar@yahoo.com</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40515">03-Dec-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">4</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Mum Support</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ilu Pilu</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ilu.Pilu@rediffmail.com</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40512">30-Nov-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">5</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="RFB Report ">RFB Report </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Uday Naik</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">getudaynaik@gmail.com</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40512">30-Nov-10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">6</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 218pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">mail reports</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Radha Diwali</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Radha.Diwali@gmail.com</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40516">04-Dec-10</TD></TR></TBODY></TABLE>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hi,

I am not sure if that can be done. Some more skilled input is required.

Try bumping if you get no further response and visiting RonDeBruins mail site.
http://www.rondebruin.nl/


Here is a Workbook Open event, which still requires you enable macros. It then triggers mail(s) immediately based on the date being found in the specified column.

You will need to change the sheet name and also set it to .send not .display as well as edit what you want in the mail.

The Code is pasted in the 'ThisWorkbook' section of the VBA project
Code: 
Private Sub Workbook_Open()
 
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim c As Long
Dim r As Long
 
    Sheets("Sheet3").Activate
 
For i = 1 To 100 '? 
    If Sheets("Sheet3").Range("E" & i).Value = Date Then
 
'Subject string
    EmailSubject = Sheets("Sheet3").Range("B" & i).Value
    EmailSendTo = Sheets("Sheet3").Range("D" & i).Value
    MailBody = "Dear " & Range("C" & i).Value _
    & vbNewLine & "Ref: Report Dated " & Range("E" & i).Value
 
 
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            '.bcc
            .Body = MailBody
            .Display
            '.send
        End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
        'MailBody = ""
 
    End If
Next i
End Sub
 
Upvote 0
A bit more:

I put my Excel file in a specified folder where I run all my tested macros from.
I set this folder as a Trusted Location from the
Office Button>Excel Options>Trust Center>TrustCenter Settings>Trusted Locations. To stop having to 'enable macros'

I then created a .BAT file on my desktop

start excel "C:\My Documents\My Macros\Email macros.xls"

So now the above runs without asking you to enable macros and sends any mails due that day.
However the file still opens.
 
Upvote 0
Hi Dave,

Thanks for the reply below, I have couple of question to ask,
the comment which you have added "I set this folder as a Trusted Location from the
Office Button>Excel Options>Trust Center>TrustCenter Settings>Trusted Locations. To stop having to 'enable macros' ", where can i find Office Buttons?

Also the outlook each time warns when sending the message ""A program is trying to automatically send e-mail on your behalf....", Can we disable the same?

Thanks for your feedback...
 
Upvote 0
Upvote 0
You may want to remove the macro code from the ThisWorkbook to a module and call it from ThisWorkbook:

Code: 
Private Sub Workbook_Open()
 
Call Mail_Report
 
End Sub

and stick the code in a normal module

Code: 
Sub Mail_Report
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim c As Long
Dim r As Long
 
    Sheets("Sheet3").Activate
 
For i = 1 To 100 '? 
    If Sheets("Sheet3").Range("E" & i).Value = Date Then
 
'Subject string
    EmailSubject = Sheets("Sheet3").Range("B" & i).Value
    EmailSendTo = Sheets("Sheet3").Range("D" & i).Value
    MailBody = "Dear " & Range("C" & i).Value _
    & vbNewLine & "Ref: Report Dated " & Range("E" & i).Value
 
 
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            '.bcc
            .Body = MailBody
            .Display
            '.send
        End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
        'MailBody = ""
 
    End If
Next i
End Sub<!-- / message -->

Probably better practice.


--------------------------------------------
This add in may or may not be of interest for those wishing to get around Outlook warnings with VBA.

http://www.add-in-express.com/outlook-security/
 
Last edited:
Upvote 0
Hi Dave,

1) I tried the code which you had giveen, but still the error of Outlook is not gone. I tried my best I am using outlook 2003. I had enterd the following small code:-
security/OlSecurityManager.DisableOOMWarnings = True
On Error Goto Finally
' ... any action with protected objects ...
Finally:
OlSecurityManager.DisableOOMWarnings = False

2) Also when i Run the batch file It opens the excel file. What I need is after the mail is sent the file should be closed automatically.

Thanks in advance
Uday
<TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE>
<TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE>
<TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE>
<TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE>
<TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE><TABLE class=dp-vb border=0 cellSpacing=0 cellPadding=0><TBODY><TR><TD class=line1>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE>OlSecurityManager.DisableOOMWarnings = True </TD></TR><TR><TD class=line2>On Error Goto Finally </TD></TR><TR><TD class=line1> ' ... any action with protected objects ... </TD></TR><TR><TD class=line2>Finally: </TD></TR><TR><TD class=line1> OlSecurityManager.DisableOOMWarnings = False </TD></TR></TBODY></TABLE>
 
Upvote 0
Hi,

1)
Firstly you have to install the add-in mentioned on the page link I sent in order to utilise that code. It sends you to this link to download the add-in
http://www.mapilab.com/outlook/security/

I haven't tested/used this as I use XL2007 and can get around that particular security warning. So I don't know how to set it up.


2)
Note: as you are automating the opening of the file if you want to close it automatically when mail sending is complete in order to view it or edit it you will need to press escape as soon as the file is opened.


Code: 
Sub Mail_Report()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim c As Long
Dim r As Long
 
 
Sheets("Sheet3").Activate
 
For i = 1 To 20
If Sheets("Sheet3").Range("E" & i).Value = Date Then
'Subject string
EmailSubject = Sheets("Sheet3").Range("B" & i).Value
EmailSendTo = Sheets("Sheet3").Range("D" & i).Value
MailBody = "Dear " & Range("C" & i).Value _
& vbNewLine & "Ref: Report Dated " & Range("E" & i).Value
 
 
'Send Mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = EmailSubject
.To = EmailSendTo
'.bcc
.Body = MailBody
'.Display
.Send
 
End With
 
Set OutMail = Nothing
Set OutApp = Nothing
'MailBody = ""
 
End If
Next i
 
'--------------Added--------
Application.DisplayAlerts = False
ThisWorkbook.Close
 
 
End Sub

The other option to get around Outlook security entirely is to use the CDO mail sending mentioned by Ron DeBruin
http://www.rondebruin.nl/cdo.htm

I have an example that works for me here:
You would need to have a play around with that and adapt it.
It will fail if you do not have an active internet connection when you run the macro.
The SMTP server needs to be one you already use I believe.

Code: 
Sub SendCDOMail()
'Avoids Outlook security
'Requires active internet connection
 
Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message
Set iCfg = New CDO.Configuration
With iCfg
.Fields(cdoSMTPServer) = "smtp.tiscali.co.uk"
.Fields(cdoSMTPServerPort) = 25 ' Usual setting
.Fields(cdoSendUsingMethod) = cdoSendUsingPort
.Fields(cdoSMTPConnectionTimeout) = 200
.Fields.Update
End With
Set iMsg = New CDO.Message
With iMsg
Set .Configuration = iCfg
.Sender = "[EMAIL="dave@msn.co.uk"]dave@msn.co.uk[/EMAIL]"
.Subject = "Test"
.TextBody = "This is a test"
.To = "[EMAIL="david.thijm@yahoo.co.uk"]david@yahoo.co.uk[/EMAIL]"
.Send
End With
Set iMsg = Nothing
Set iCfg = Nothing
End Sub
 
Upvote 0
Hi Dave,

the code for closing the workbook is working fine. But the Outlook pop-up is still showing. I had been through the web-site which you had provided. I have pasted the same in workbook. Also I tried for Sendkey, but it shows an error "Microsoft Office WordMail could not be started. Close any open dialog oxes in Word and try again.". But its not working. Is the code which I have put correct?

Private Sub Workbook_Open()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
' Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
With iMsg
Set .Configuration = iConf
.To = "uday.naik@orange-ftgroup.com"
.CC = ""
.BCC = ""
.From = """Uday"" <uday.naik@orange-ftgroup.com>"
.Subject = "New figures"
.TextBody = strbody
'.Send
End With

Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim c As Long
Dim r As Long

Sheets("Sheet1").Activate

For i = 1 To 100 '?
If Sheets("Sheet1").Range("E" & i).Value = Date Then

'Subject string
EmailSubject = Sheets("Sheet1").Range("B" & i).Value
EmailSendTo = Sheets("Sheet1").Range("D" & i).Value
MailBody = "Dear " & Range("C" & i).Value _
& vbNewLine & "Ref: Report Dated " & Range("E" & i).Value


'Send Mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = EmailSubject
.To = EmailSendTo
.CC = "uday.naik@orange-ftgroup.com"
'.bcc
.Body = MailBody
.Display
'.Send
Application.Wait (Now + TimeValue("00:00:09"))
Application.SendKeys "%s"
SendKeys String:="{ENTER}", Wait:=True
End With
Set OutMail = Nothing
Set OutApp = Nothing
'MailBody = ""

End If
Next i

End Sub
 
Upvote 0
Hi,

My apologies, I did forget one thing though, which won't have helped when using CDO!

In the VBA editor select Tools>Referneces and tick the box
'Microsoft CDO for Windows 2000 library'

If you cannot get rid of the Outlook mail warning then there is an option to send mail using CDO instead. It doesn't use Outlook to do this.
You seem to have a mix of the two in your code.

Read Rons link
http://www.rondebruin.nl/cdo.htm

However you can look into your Outlook account to get your SMTP server name, which is required for the code to work.


Below is an example adapted with some of your parameters.

1. You must have
'Microsoft CDO for Windows 2000 library' selected
2. You must have an active internet connection
3. You must use your own SMTP server.
4. Forget Outlook

Code: 
Sub SendCDOMail()
 
Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message
Sheets("Sheet1").Activate
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
For i = 1 To 10
If Sheets("Sheet3").Range("E" & i).Value = Date Then
'Subject string
EmailSubject = Sheets("Sheet1").Range("B" & i).Value
EmailSendTo = Sheets("Sheet1").Range("D" & i).Value
 
Set iCfg = New CDO.Configuration
With iCfg
.Fields(cdoSMTPServer) = "smtp.tiscali.co.uk" '--------USE OWN SMTP server. To find what you are using look into your Outllok account settings.
 
 
.Fields(cdoSMTPServerPort) = 25 ' Usual setting
.Fields(cdoSendUsingMethod) = cdoSendUsingPort
.Fields(cdoSMTPConnectionTimeout) = 200
.Fields.Update
End With
Set iMsg = New CDO.Message
With iMsg
Set .Configuration = iCfg
.Sender = """Uday"" <[EMAIL="uday.naik@orange-ftgroup.com"]uday.naik@orange-ftgroup.com[/EMAIL]>"
.Subject = EmailSubject
.TextBody = strbody
.To = EmailSendTo
.Send
End With
Set iMsg = Nothing
Set iCfg = Nothing
End If
Next i
End Sub

The benefit of CDO is you don't get any security warning dialogs that you get with Outlook.
A minor disadvantage is you won't see the mail going through the send process.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
706
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
524
esben
E
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
956
epactheactor
E

Forum statistics

Threads
1,225,155
Messages
6,183,217
Members
453,151
Latest member
Lizamaison

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