Share via

Can I send automatically send monthly VBA CDO emails?

Anonymous
2016-02-20T17:59:55+00:00

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 now 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") = "******@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 = "******@yahoo.com"

.To = "******@hotmail.com"

.CC = ""

.BCC = "******@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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-02-26T11:42:14+00:00

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-02-22T15:36:46+00:00

    One other minor problem which I hope is easier to solve:  I have developed the ".TextBody =" line to complete my report and it is now just over the maximum number of characters allowed for that line.  Is there an easy way of doing a second .TextBody to immediately follow the first.  I don't want to do an attachment as I want this to be a short monthly report easily readable in plain text on a mobile device.  I really am only a few characters over the maximum allowed and hope there is an easy solution.

    Again, all help gratefully received.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-02-22T15:31:35+00:00

    Thanks for this.  I'm very new to this, but happy to work things out if I have some pointers.  Unfortunately, I'm probably trying to do something quite advanced when I don't yet know the basics.

    It was suggested on another forum (which Google now says is too dangerous to open) that I need to make a sub run every so often to check if its the first of the month and then send the email if one hasn't been sent already.  And that I also need a second sub to check if an email has been sent that month every time the spreadsheet is opened. 

    If others could validate the best way of doing this then once this is decided, perhaps someone could point me at a resource to help me learn how to code this from scratch.

    All help gratefully received!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-02-22T13:05:49+00:00

    Disclaimer: I have not adopted XL2016, so I'm suggesting ideas that have worked in earlier versions of Excel

    ...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...

    To have something happen when the workbook is opened, look to the Workbook_Open event. Note this will only trigger when this specific workbook is opened, not when Excel is loaded or when other workbooks are opened... so you are still dependent on the user.

    I would look at the workbook custom properties, and create a flag that stores the date that the report was last produced. In Workbook_open, check this flag, check today's date, determine if the report should be generated, then update the flag and save the workbook before giving control back to the user. 

    http://www.cpearson.com/excel/docprop.aspx

    https://msdn.microsoft.com/en-us/library/office/ff834990.aspx

    If you have the same workbook go to multiple users, each one will generate a report independently, in which case you'd get multiple copies of the report emailed. 

    I'm sure other folks will have additional ideas, this is just the first thing that came to mind for me.

    Was this answer helpful?

    0 comments