Hi there!

I started working out how to use Excel VBA to send an email about 3 days ago, and so please be a little patient with a novice...

After some experimentation and borrowing code on the internet, I worked out that sending emails using CDO is best as it doesn't require an authentication click from the user. I also worked out how to format a very simple .TextBody email message so that I can extract cell info from Excel and put some simple text around this, although I don't yet know how to set the fonts for the text. The good news is that I finally have emails sending with a Yahoo email address (having first tried many online solutions that didn't work) and I'm very pleased to have got this far.

What I would now like to do is to work out how to get this email report to send to me automatically at the start of each month, and if the spreadsheet is not open on the first of the month then for the email to send as soon as the spreadsheet is next opened. I'm not sure if this kind of functionality exists within Excel, but if I can get this to work it would be really great as I would be able to automatically generate emailed monthly reports for all kinds of data.

I would be very grateful for some help with this as Googling isn't helping me!

Many thanks, Peter


My existing VBA code to send an email using CDO (and which finally works!) is as follows:


Sub SendEmailUsingYahoo()

Dim NewMail As CDO.Message

Set NewMail = New CDO.Message

'Enable SSL Authentication
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Make SMTP authentication Enabled=true (1)

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.yahoo.com"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Set your credentials of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxx@yahoo.com"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxx"

'Update the configuration fields
NewMail.Configuration.Fields.Update

'Set All Email Properties

With NewMail
.Subject = "xxx Portfolio – Monthly Report"
.From = "xxx@yahoo.com"
.To = "xxx@hotmail.com"
.CC = ""
.BCC = "xxx@yahoo.com"
.TextBody = "The value of your portfolio is US$" & Range("'Portfolio Dashboard'!E48").Value &"m"
& vbNewLine & "Value of debt is US$" & Range("'Portfolio Dashboard'!J48").Value &"m"

End With


NewMail.Send
MsgBox ("Mail has been Sent")

'Set the NewMail Variable to Nothing
Set NewMail = Nothing

End Sub