How to call a Function in a string in VBA

Question

I have a MS Access database in which I want to call a function which has today's date in a string as below. I am trying to put today's date in the subject line and the email body on the new email which will be created. Any ideas to achieve this?

Private Sub Command_Click()

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim ThisDay As Date

ThisDay = Format(Now, "mm/dd/yy")    

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
    .To = ""
    .CC = ""
    .Subject = "Daily Email Processed " *(Thisday)
    .Body = "Hi," + vbNewLine + vbNewLine + vbNewLine + "Please find below the number of Emails processed for the  " + vbNewLine + vbNewLine + "Email Count = " + vbNewLine + "O Count = "
    .Attachments.Add ""
    .Attachments.Add ""
    .Display        
End With

Set objOutlook = Nothing

End Sub

Format() returns a string, so Dim ThisDay As String. Do you have other issues with that code? Do you get an error? — HansUp
– HansUp, Commented Feb 12, 2013 at 5:55
Ok thanks for that but how will I call it in .Subject = "Daily email count" & Thisday& like this or what? — CrashOverride
– CrashOverride, Commented Feb 12, 2013 at 6:01
It is usually simpler to compose a mailto://.. url with subject, body, recipents etc, and let the OS resolve a mail client to edit the message before sending. See email.about.com/od/mailtoemaillinks/a/mailto_elements.htm, and make sure you UrlEncode all data before adding to the url. — John Alexiou
– John Alexiou, Commented Feb 12, 2013 at 6:12
since i am doing this in ms access i am sorry but i don't know how to do the way you are telling me :( @ja72 — CrashOverride
– CrashOverride, Commented Feb 12, 2013 at 6:19

HansUp · Accepted Answer · 2013-02-12 07:01:35Z

2

You intend to store a Format() expression in your ThisDay variable. Format() returns a string, so declare the variable to match.

'Dim ThisDay As Date
Dim ThisDay As String

After you've loaded the formatted date string into ThisDay, you can concatenate it to the rest of the subject line.

'.Subject = "Daily Email Processed " *(Thisday)
.Subject = "Daily Email Processed " & Thisday

answered Feb 12, 2013 at 7:01

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to call a Function in a string in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related