Macro to create Outlook email with email signature - Qlik Community

Report Inappropriate Content · ‎2012-02-08

I managed (with a lot of help from other forum posts) to create a macro that creates an email in Outlook and includes the default outlook signature. I've included the code below. To get this to work I had to create variables in the Document Properties and set the variables to the values I wanted using fields from the selected record.

I select the record I want, which fills the values in the variables and then I click a button which executes the macro and generates the email with the "TO:", "BCC", "SUBJECT", "BODY" and default outlook email signature.

This is working well but I was wondering if there was a way to directly reference QV fields in the macro rather than use the function to retrieve the variable values e.g. is there a way to say something like this, where "ContactEmailAddress" is the QV field and not a variable name

With objOutlookMsg
.To = ContactEmailAddress

Sub SendSurveyResponse()

SigString = "C:\Users\USERNAME\AppData\Roaming\Microsoft\Signatures\Signature.htm"

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(SigString).openastextstream(1, -2)
VSignature = ts.readall

Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
.To = getVariable("ResponseTO")
.BCC = "mark.boomer@microfocus.com"
.Subject = getVariable("ResponseSUBJECT")
.HTMLBody = vSignature
.htmlBody = "<html><body>" & getVariable("ResponseBODY") & vSignature & "</body></html>"
.Display
End With

Set fso = nothing
Set ts = nothing

Set objOutlook = nothing
Set objOutlookMsg = nothing

End Sub

function getVariable(varName)
set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
end function

Anonymous · ‎2012-10-04

If I understand correctly what you're asking, then below is a few different ways I think may be applicable to your requirements that I got to successfully reference a qv field in the macro script. Ironically, the problem I had in a comparable situation was trying to figure out how to use the variable which I've since achieved, but I hadn't thought about using a function. Either way, I had a hard time finding information about this for my needs when I first learned to do it so the verbose explanation is to help others as well. Also, I was applying this was for exporting purposes so it may need a little massaging to align with your purposes - maybe - maybe not.

I've also included a demo application with the information provided below.

I guess the short answer would be:

ACTIVEDOCUMENT.FIELDS("your field").SELECT "value within your field"

DEMO INFORMATION:

Put this in the vbScript module macro and create put with action to run it.

sub selectInput

'this will set the vbscript variable "FIELDNAME" to the qv field "Month" and then select the month (qv record of the qv field) provided from the user via input prompt

set FIELDNAME = activedocument.fields("Month")

fname = inputbox("Please input the desired month to select and click OK." & vbCrLF & vbCrLF & "MUST USE FIRST 3 LETTERS OF MONTH ONLY WITH FIRST LETTER CAPITALIZED." & vbCrLF & vbCrLF & "Ex: Acceptable = Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec", "Month Input", "Sep")

FIELDNAME.SELECT fname

'same thing but will allow for another input driven qv field selection in addition to the previous selection

set FIELDNAME = activedocument.fields("Year")

fname = inputbox("Please input the desired year to select (either 2012 or 2011) and click OK.", "Year Input", "2012")

FIELDNAME.SELECT fname

'this is the same in that it is yet another compounding selection to the previous selections in qv but different in that it does not give the user the ability to determine the selections at this point as it had done in the Month and Year.

'In addition, this is also utilizing the toggleselect in the qv field. It first makes a selection and then holds that while toggle selecting other values in the field.

'The key with toggleselect is that you have to make a single selection first before starting the toggle selections.

set PLAN = ACTIVEDOCUMENT.FIELDS("Region")

PLAN.SELECT "Region1"

PLAN.TOGGLESELECT "Region2"

PLAN.TOGGLESELECT "Region3"

PLAN.TOGGLESELECT "Region4"

PLAN.TOGGLESELECT "Region5"

'In total, you would have three fields with selections. One selection for Month, One selection for Year, and Five selections for Region

'Any of the methods to directly reference a qv field and make a selection are applicable by themself

end sub

Put this in the load script to demo.

Table:

LOAD * INLINE [

Year, Month, Region

2012, Jan, Region1

2012, Feb, Region1

2012, Mar, Region1

2012, Apr, Region1

2012, May, Region1

2012, Jun, Region1

2012, Jul, Region1

2012, Aug, Region1

2012, Sep, Region1

2012, Oct, Region1

2012, Nov, Region1

2012, Dec, Region1

2011, Jan, Region1

2011, Feb, Region1

2011, Mar, Region1

2011, Apr, Region1

2011, May, Region1

2011, Jun, Region1

2011, Jul, Region1

2011, Aug, Region1

2011, Sep, Region1

2011, Oct, Region1

2011, Nov, Region1

2011, Dec, Region1

2012, Jan, Region1

2012, Feb, Region1

2012, Mar, Region1

2012, Apr, Region1

2012, May, Region2

2012, Jun, Region2

2012, Jul, Region2

2012, Aug, Region2

2012, Sep, Region2

2012, Oct, Region2

2012, Nov, Region2

2012, Dec, Region2

2011, Jan, Region2

2011, Feb, Region2

2011, Mar, Region2

2011, Apr, Region2

2011, May, Region2

2011, Jun, Region2

2011, Jul, Region2

2011, Aug, Region2

2011, Sep, Region2

2011, Oct, Region2

2011, Nov, Region2

2011, Dec, Region3

2012, Feb, Region3

2012, Mar, Region3

2012, Apr, Region3

2012, May, Region3

2012, Jun, Region3

2012, Jul, Region3

2012, Aug, Region3

2012, Sep, Region3

2012, Oct, Region3

2012, Nov, Region3

2012, Dec, Region3

2011, Jan, Region3

2011, Feb, Region3

2011, Mar, Region3

2011, Apr, Region3

2011, May, Region3

2011, Jun, Region3

2011, Jul, Region3

2011, Aug, Region3

2011, Sep, Region3

2011, Oct, Region3

2011, Nov, Region3

2011, Dec, Region3

2012, Feb, Region4

2012, Mar, Region4

2012, Apr, Region4

2012, May, Region4

2012, Jun, Region4

2012, Jul, Region4

2012, Aug, Region4

2012, Sep, Region4

2012, Oct, Region4

2012, Nov, Region4

2012, Dec, Region4

2011, Jan, Region4

2011, Feb, Region4

2011, Mar, Region4

2011, Apr, Region4

2011, May, Region4

2011, Jun, Region4

2011, Jul, Region4

2011, Aug, Region4

2011, Sep, Region4

2011, Oct, Region4

2011, Nov, Region4

2011, Dec, Region4

2012, Feb, Region4

2012, Mar, Region4

2012, Apr, Region5

2012, May, Region5

2012, Jun, Region5

2012, Jul, Region5

2012, Aug, Region5

2012, Sep, Region5

2012, Oct, Region5

2012, Nov, Region5

2012, Dec, Region5

2011, Jan, Region5

2011, Feb, Region5

2011, Mar, Region5

2011, Apr, Region5

2011, May, Region5

2011, Jun, Region5

2011, Jul, Region5

2011, Aug, Region5

2011, Sep, Region5

2011, Oct, Region5

2011, Nov, Region5

2011, Dec, Region5

];

Hope this helps. Let me know if you ever find a way to use your email method with Lotus Notes!