+ Reply to Thread
Results 1 to 5 of 5

Can I automatically send a monthly email with Excel VBA?

  1. #1
    Registered User
    Join Date
    02-20-2016
    Location
    Hong Kong, Hong Kong
    MS-Off Ver
    2016
    Posts
    10

    Can I automatically send a monthly email with Excel VBA?

    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") = "[email protected]"

    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 = "[email protected]"
    .To = "[email protected]"
    .CC = ""
    .BCC = "[email protected]"
    .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

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Can I automatically send a monthly email with Excel VBA?

    The simple answer is yes.

    It is a bit more complex that you think, but it is possible.

    First you will need to create a log. Where would you want to save that? Possible in the same folder as your Personal Excel Workbook.#

    Do you know what your personal Excel workbook is?

    Then you would need to create two macros in your personal workbook.

    One to run hourly and to send your email if it is the first of the month and your log is empty

    The other to run when you open excel and to send the email if the log is empty.

    I will create something for you.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    02-20-2016
    Location
    Hong Kong, Hong Kong
    MS-Off Ver
    2016
    Posts
    10

    Re: Can I automatically send a monthly email with Excel VBA?

    Many thanks indeed, this would be really great. The workbook is just on my OneDrive and I can put it in its own folder and save the log in that folder. Would this work?

    I previously had no idea that this sort of thing was possible with Excel...Best, Peter

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Can I automatically send a monthly email with Excel VBA?

    Ok this is still work in progress.

    Paste this code into your personal workbook.

    Replace "*************" with your email address.


    Then run SheduleLog.

    When run the Macro will look for the Schedule Log in the Folder housing your Excel Startup Folder.
    It will not find it so it will create it.

    I have set it up to create three Schedule Entries. Two of which are set to run.

    Running the Macro a second time will send you Two Emails.

    I have set it to send you a copy of the Schedule as both an attachment and also posted into the body of the email.

    Changing the File name in the Schedule Log would result in another file being sent.

    Running the Macro UpdateShedule will open the Schedule for you to Edit.
    But remember to delete the mart of the main macro that is filling it with dummy data.


    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-20-2016
    Location
    Hong Kong, Hong Kong
    MS-Off Ver
    2016
    Posts
    10

    Re: Can I automatically send a monthly email with Excel VBA?

    Thanks so much and I will try this out and let you know how I get on.

    This really is much appreciated. Peter

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Have excel automatically send an email when a date is near
    By MCCranes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2014, 09:16 AM
  2. Need help with getting excel to automatically send out email....
    By glynjara in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-28-2013, 06:57 PM
  3. Excel to send Email automatically when due date is approaching
    By EugeneE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-25-2013, 06:34 PM
  4. Replies: 0
    Last Post: 04-04-2012, 09:21 AM
  5. Excel to automatically send an email from Outlook when a condition is met
    By Spyke in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-14-2011, 04:40 AM
  6. Send email automatically through excel with attachement and picture
    By yorkshirewhite in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-24-2011, 11:24 AM
  7. Need Excel to send Automatically an Email
    By japorms in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-20-2011, 02:57 AM
  8. [SOLVED] Send email automatically in excel
    By [email protected] in forum Excel General
    Replies: 0
    Last Post: 03-23-2005, 01:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1