Adding Variable data to an email subject line

[Adendum]

Hi,
I am using this VBA to send a worksheet and I would like to be able to offer an option to add a variable to the subject line so we can distinguish emails...

Sub SendWorkBook()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    On Error Resume Next
    With OutlookMail
.Display
.To = "email@email.com"
.CC = ""
.BCC = ""
.Subject = "Training-Project"
.Body = "See attached. Respond ASAP. Thank you."
.Attachments.Add Application.ActiveWorkbook.FullName
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

So Ideally the user is offered a message box to enter a word or phrase, probably a location, and the subject line appends that word or phrase to it so that the email subject line would read...

"Training-Project (London)" but someone in York would send a mail "Training-Project (York)".

 

[Kenneth Hobson]

.Subject =  "Training-Project (" & InputBox("Location? e.g. York", "Location Input") & ")"

 

[Adendum]

.Subject =  "Training-Project (" & InputBox("Location? e.g. York", "Location Input") & ")"

Kenneth,

That is bordering on brilliant...and so simple...even I can understand it!

The downside is that when the button is clicked the Outlook email window covers the message box. It works but you have to minimise the Outlook email, then enter the details into the message box and switch over to the Outlook window to see the email populated. It works though!

Maybe I should look at generating a message box first when the button is clicked and then open up Outlook? Wonder if I can figure that bit out? Feel free to prompt a novice!

 

[Adendum]

Kenneth,

That is bordering on brilliant...and so simple...even I can understand it!

The downside is that when the button is clicked the Outlook email window covers the message box. It works but you have to minimise the Outlook email, then enter the details into the message box and switch over to the Outlook window to see the email populated. It works though!

Maybe I should look at generating a message box first when the button is clicked and then open up Outlook? Wonder if I can figure that bit out? Feel free to prompt a novice!

Worry not .... now have a message box seen first, takes it's info and dumps in to the subject line.

Very happy bunny here

 

[Kenneth Hobson]

Glad all is well.

Most usually just .Send so no Outlook is shown. Of course if you want the user to edit the email and send it themselves, then .Display is best.