I've been back and forth through previous posts and trawled the internet for VB Script guidance.
I therefore throw myself on your mercy!
I want a user to select a customer from a drop down list.
Then they need to press a button (run a macro) to generate an email to send to the relevant Sales Representative.
I want the email address to come from a table in QV and the subject to read " Sales Report for " & CustomerName
Macro code is as follows:
SUB openMail
Set vOlApp = CreateObject("Outlook.Application")
Set vMessage = vOlApp.CreateItem(olMailItem)
'=====================================
'Setting the email recipient
vMessage.To = "some.one@email.com"
vMessage.Display
End SUB
My problem comes with setting the Subject
When I set it as hard text (like the email as above) it works:
vMessage.Subject = "Sales Report for"
So does getting the document name from QV:
vSubText = ActiveDocument.Name
vMessage.Subject = "Sales Report for " & vSubText
But obviously the DocumentName isn't the CustomerName I need
I tried using the SheetName (set as a formula ='Sales Report for '&CustomerName):
vSubText = ActiveDocument.ActiveSheet.GetProperties.Name
vMessage.Subject = "Sales Report for " & vSubText
But this just gives me an email subject of "='Sales Report for '&CustomerName"
I've tried copying and pasting a Text Box (TX01 = CustomerName):
ActiveDocument.GetSheetObject("TX01").CopyTextToClipboard
'vMessage.Subject.Paste
No luck
I tried linking directly:
vMessage.Subject = ActiveDocument.GetSheetObject("TX01")
Again failed.
I even tried creating a new variable:
vText = ActiveDocument.GetSheetObject("TX01")
vMessage.Subject = vText
But this doesn't work
Nor does:
vText= "yippee"
vMessage.Subject = vText
So that can't work.
Once I have a solution to this I would like to use the same method for the Sales Representative's email.
I'm running out of patience and time so any advice that you can offer would be appreciated
In Hope
Sam
