1 Reply Latest reply: Oct 4, 2012 10:12 AM by Curt Daughtry RSS

    Macro to create Outlook email with email signature

      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
        • Re: Macro to create Outlook email with email signature
          Curt Daughtry

          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!