Posts tagged Outlook

Personalised Email Templates – VBA

0 Comments read Quick Fixes, Solutions

Are you sending the same email over and over again, with just a few alterations? I know I do, and one of the easiest ways is to open an oft template and then change some words.

Adding a Macro

You can use this post to see how to add macros, and how to enable a button for it:

Enabling Macros in Outlook

The Solution

This is a simple two part solution:

1. The Email Template

Which has [TARGET] words that will be replaced from questions that are asked by input boxes.

2. The VBA Macro

This will open the Macro, ask you some Inputs, and then replace the [TARGET] words with what you entered in the inputs.

The Template

This is the easy part, just build an email then opt to Save As the message, as a template (*.oft). This is the example of the one used by the code. You’ll see three uses of [Gift] and one of the [Gifter] target words.

I’ve used a word inside the square brackers [TARGET]

The Code

Theres a lot you can do here, but I’ve opted for the basics – targeting words in the subject line and the body of the message.

You run the Coffee_Thanks() as a Quick Access button, which for each replacement the script calls the replaceText() sub.

Sub Coffee_Thanks()
    
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    '  VARIABLES  -------------------------------------------------------------------------------'
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
        'DATA----------------------------------------  
        Dim CoffeeAmount As String 
        Dim personName As String 
        'TEMPLATE------------------------------------ 
        Dim newItem As Outlook.MailItem  
        'REPLACEMENTS-------------------------------- 
        Dim wdDoc As Object 
        Dim olItem As Object 
        Dim olInsp As Outlook.inspector 
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'




    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    '  GATHER INFO ------------------------------------------------------------------------------'
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
       ' Gather additional information-------------- 
        CoffeeAmount = InputBox("Coffee Gift in $:")    
        personName = InputBox("Recipients name:")
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
   
    
    
    
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    '  OPEN TEMPLATE ----------------------------------------------------------------------------'
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
        ' open Template------------------------------  
        Set newItem = Application.CreateItemFromTemplate("D:\Templates\Outlook\Coffee_Thanks.oft")
        newItem.Display
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    
    
    
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    '  TEXT REPLACEMENTS ------------------------------------------------------------------------'
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
       ' Set up for changes-------------------------  
		   ' Word Swaps in Subject line-----------------  
				Set olInsp = Application.ActiveInspector  
				Set olItem = olInsp.currentItem  
			 ' Word swaps in Body--------------------------
				Set wdDoc = newItem.GetInspector.WordEditor 

		   ' Replace all placeholders-------------------   
        olItem.Subject = Replace(olItem.Subject, "[Gift]", CoffeeAmount) 
        Call replaceText(wdDoc, "[Gifter]", personName)
        Call replaceText(wdDoc, "[Gift]", CoffeeAmount)
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    
End Sub

Sub replaceText(wdDoc As Object, findText As String, replaceText As String)
	'+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    '+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    ' Repeatable loop to replace words in the body, without altering
    ' the HTML formatting.
	'+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-'
    With wdDoc.Content.Find
        .Text = findText
        .Replacement.Text = replaceText
        .Execute Replace:=2 ' 2 = Replace all
    End With
End Sub

NOTE: I’ve saved my oft template to “D:\Templates\Outlook\Coffee_Thanks.oft”, you will need to change the code to point to where ever you have saved the template.

There’s a mountain different ways to use this, and you can do various other things like:

  • Calculate the time of day to tweak the greating to a good morning, or afternoon.
  • Find out when the next weekend is, because you’re using this to warn about patching.
  • Add people to the CC or BC fields.
  • Use input choices to add different content.

Download the Code

WordReplacement.vb https://github.com/Works4Me-Info/Outlook-Macros

Enabling Macros in Outlook

1 Comment read Tips and Tricks

I love macros, they are the easiest way to automate your tasks, and if you’re on my site you know I love to automate repeated tasks. Problem is doing that in Outlook is doing it safely. I personally don’t enable macros unless I’ve personally looked at the code.

With that in mind, I have some macros that automate emails and signatures (some of my signatures use layered images and text, which the default Outlook signatures can’t handle). Before we do that I wanted to walk through how to set some security up for doing that.

We will disable Macros, and have Outlook notify us if we use a macro with a certificate (one you PC already knows about and trust, but publising the certificate ourselves). This way we know it isn’t some random Macro, it’s one we wrote.

Step One: Developers Tab

We are going to need the Developers tab for this. Hopefully you already know how to do this one, but just in case, from within Outlook:

  • File > Options > Customize Ribbons.
  • Tick Developer.
  • Click OK.
Developers Tab Selection
Developer Tab

Step Two: Set the Security

  • From the Developers Tab click the Macro Security button.
  • Set the Macro Settings to Notifications for digitally signed macros, all other macros disabled.
  • Click OK.
macro Security

Step Three: Add your Macro

  • From the Developers Tab click the Visual Basic button , then  Insert > Class Module.
  • Insert you VBA Macro.
Sub OpenW4M_Signature()
    Dim MyItem As Outlook.MailItem
     
    Set MyItem = Application.CreateItemFromTemplate("D:\Templates\Outlook\SomeGuy-Signature.oft")
    MyItem.Display

End Sub
Example Code

This is a simple macro to open a oft or Message Template. To make a oft file, just create an email with everything you want it to have, then go File > Save As and change the type to Outlook Template (*.oft).

  • Click Save.
  • Close Outlook and Save the VbaProject.OTM is asked.
Clicking Visual Basic

Step Four: Create a Certificate

  • Run the following:
    • C:\Program Files\Microsoft Office\root\Office16\Selfcert.exe
    • Note: This path will depend on the first version of Office you have installed, so you might have to search for Selfcert.exe in your C:\Program Files\Microsoft Office\ folder.
  • Enter a relevant Name and click OK.
  • Click OK on the Successful Creation Message.

Step Five: Assign the Certificate

  • Open Outlook again.
  • From the Developers Tab click the Visual Basic button.
  • With your VbaProject.OTM selected, click Tools > Digital Signatures…
  • Click the Choose… button.
  • Click OK , if you certificate is shown (most likely, or use more choices to find it).
  • Click OK.
  • Close Outlook – and click yes to save VbaProject.OTM.

Step Six: The Loop

Getting that certificate to stick is the issue. I’ve had real trouble applying the certificate saving, closing Outlook and reopening it, only to five my Vbaproject.OTEM doesn’t have the certificate applied, and you may need to repeat Step 5 a few times – The last time I assigned a new certificate helping someone, it only took 1 repeat for it to take effect.

Once this is working, you will see this, the first time you run a macro each time you open Outlook. If you leave Outlook open for days/weeks – you will only see it that first time.

Step Seven: Adding it as a Button

  • For this Macro you want it to be in the Outlook Quickbar, but some of the Macros I’ll share you would add to the Quickbar of a Message itself, just click on the drop down in your Outlook bar and click More Commands…
  • From the Choose commands from drop down, select Macros.
  • Select you Macro click the Add>> button, then with it selected on the right, click the Modify… button to choose a better icon.
  • Then Click OK.

Now at the click of a button – I get my email opened with a super customised signature.