1

I am new to the VBA format of writing code and I have been researching how to get the current date to AUTO input into the Subject line of an email when commanded. I need to know how to complete the following task.

Subject line should read, "stuff stuff stuff TODAYS DATE" Body should read, "Person, (return; tab) Stuff stuff stuff TODAYS DATE"

Also, if its possible I would like it to auto attach a file from a specific location that will have the name of "TODAYS DATE.xlsx"

Todays date should be in the format of DDMMMYY.

I am currently using a very basic macro to create the email and add the text where I need it.

Sub STUFF()
Dim msg As Outlook.MailItem
Set msg = Application.CreateItem(olMailItem)
msg.To = "EMAILS"
msg.CC = "EMAILS"
msg.Subject = "STUFF STUFF STUFF *DATE*"
msg.Body = "PERSON, STUFF STUFF STUFF *DATE*"
msg.Display
Set msg = Nothing
End Sub

Thanks for the help.

edit - For clarrification I have tried the following: Today's date in Outlook Mail Subject (2010/Quick Steps) https://stackoverflow.com/questions/9177199/getting-the-current-date-in-visual-basic-2008 https://stackoverflow.com/questions/4313730/current-date-in-outlook-subject-line And most of the other Google Results for any variation of the keywords "macro outlook 2010 current date"

edit 2 - Reason My end goal is to have a Macro command, that when selected, creates a new email with the To, CC, Subject, Body, and Attachment completed for me so that I can just edit a couple lines of daily changing information and send it off. This will only be used when needed and not everytime I need to send a new email.

  • possible duplicate of Today's date in Outlook Mail Subject (2010/Quick Steps) – DavidPostill Nov 4 '14 at 16:29
  • I tried that method, it didn't work. It says "Object required" and wont let me continue. If you could clarrify on what im missing, that would be helpful. – J.Seal Nov 4 '14 at 16:29
  • 1
    Then please let us know what you have tried. From How do I ask a good question?. "Sharing your research helps everyone. Tell us what you found and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and above all, it helps you get a more specific and relevant answer!" – DavidPostill Nov 4 '14 at 16:31
  • Thats the issue. Quick step won't allow for anything more than setting up the email to BE written. I am trying to use the Macro funcion to complete all of the tasks at once. – J.Seal Nov 4 '14 at 17:23
2

To accomplish the title of your post you are looking for Now with some date manipulation.

Sub STUFF()
    Dim msg As Outlook.MailItem
    Set msg = Application.CreateItem(olMailItem)
    msg.To = "email@email.ca"
    msg.CC = "BumblebeeJoe@email.ca"
    msg.Subject = "STUFF STUFF STUFF" & Now
    msg.Body = "PERSON, STUFF STUFF STUFF" & Now
    msg.Display
    msg.Attachments.Add ("e:\temp\" & Format(Now, "dd.mm.2014") & ".xlsx")
    Set msg = Nothing
End Sub

Now would just return the date which in our case is just appended to the strings for Subject and Body. If you are looking to format the date you would use something like Format(Now, "dd.mm.2014") that you see in the Attachment.Add example. More date formatting examples

As for the attachment that is fairly simple. I have not done any data validation for paths which you should do but I ended up with the following in my test. This also entirely depends on your file naming conventions.

enter image description here

Then all you would need to do is something like assign a quick access toolbar button to your macro.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.