Email a report through Macro

soton34 — Thu, 13 Oct 2011 12:57:59 GMT

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

Re: Email a report through Macro

masha-ecraft — Sat, 03 Mar 2012 13:24:20 GMT

Hi Sam,

you can get the customer name via a variable and then assign its content to the subject

oMailDoc.Subject = "Sales Report for " & ActiveDocument.Variables("SelectedCustomer").GetContent.String

The variable can be defined in Qlikview document properties:

only(CustomerName)

Regards,

Masha

topic Re: Email a report through Macro in QlikView

Email a report through Macro

Re: Email a report through Macro