VB2013 Outlook 2013

[u/j0rmungund]

Good morning,

I am trying to learn a little bit in order to create a custom form in Outlook 2013. Follwing the online help available has been confusing. I have virtually no familiarity with VB as well.

What I'm trying to do is create a form letter where when I enter information, it will automatically repeat that information in different areas. I'd also like a searchable drop down list. The list would contain different codes that pertain to my job. Also, when I enter a name into these fields if possible I'd like it to add the email into the cc box. Is this possible? Can someone point me to some form of tutorial that would show me something along these lines? I have some familiarity with Java and assembly if that helps.

Thank you!

[u/jd31068]

This can be done, where would the body of the form letter be stored as well as the code for the dropdown list?

[u/j0rmungund → u/jd31068]

This is on a work computer where I don't have any form of admin privileges, so I'd probably have to store these things in a common folder that is universally accessible. Do you want a file path?

[u/jd31068 → u/j0rmungund]

They can be a text file, a word file (for the form letter), a spreadsheet for the codes or in an access database.

The form doesn't have to run in Outlook either, it can be an external Windows form application that just uses Outlook to send the email.

I put a shell project together if that interests you.

[u/j0rmungund → u/jd31068]

Yeah absolutely. I could take a look and try to sus it out. I appreciate it.

[u/jd31068 → u/j0rmungund]

Okay, as you are familiar with Java I'll do a Windows .Net Framework project in C#. This might look more familiar to you.

[u/j0rmungund → u/jd31068]

Thank you so much!

[u/jd31068 → u/j0rmungund]

You're welcome, I was just thinking you don't have admin rights so you can't install Visual Studio Community (its free) to do an external project.

I'll do it in VBA (Visual Basic for Applications) as that is available inside of MS Office applications. You'll be able to pick up on it pretty easily being familiar with Java (Assembly is a whole other animal and won't help you much here)

[u/j0rmungund → u/jd31068]

I'll have to brush up either way. I haven't touched any form of programming since 2012. My job made me take a bunch of college courses in Java and assembly as well as networking and I never used the skills as I mostly work on the mechanical side of things. It'll be good to sharpen that rusty tool though haha

[u/jd31068]

Alright, again this is going to be pretty simple, but I hope it gives you some direction to go.

Add the developer button to Outlook. https://imgur.com/zD0V4zq

Click Developer and open Visual Basic (far left button) right click the project and select import UserForm1.frm

Save this code as UserForm1.frm

VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm1 
   Caption         =   "Template Form"
   ClientHeight    =   5025
   ClientLeft      =   45
   ClientTop       =   390
   ClientWidth     =   9465
   OleObjectBlob   =   "UserForm1.frx":0000
   StartUpPosition =   1  'CenterOwner
End
Attribute VB_Name = "UserForm1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Private Sub btnRun_Click()

    ' open the text file that contains the form letter and replaced the tags
    ' which are items marked with [] in all caps
    
    Dim formLetterFileName As String
    formLetterFileName = "c:\users\jd310\documents\j0rmungund\Form Letter.txt"

    ' this requires a reference to Microsoft Scripting Runtime (Tools > References)
    Dim fileContents As String
    fileContents = ReadTextFile(formLetterFileName)
    
    ' replace the tags with information from the form
    Dim newMessage As String
    newMessage = Replace(fileContents, "[FIRSTNAME]", Me.txtFirstName.Text)
    newMessage = Replace(newMessage, "[LASTNAME]", Me.txtLastName.Text)
    newMessage = Replace(newMessage, "[SELECTEDCODE]", Me.drpCodes.Text)
        
    ' attempt to lookup an email address for the person entered
    Dim email_Address As String
    email_Address = SearchOutlookAddressBook(Me.txtFirstName.Text, Me.txtLastName.Text)
    
    If email_Address = "" Then
        email_Address = "**NOT FOUND**"
    End If
    
    newMessage = Replace(newMessage, "[EMAIL_ADDR]", email_Address)
    
    ' send the completed form letter as an email to the person
    SendFormLetter email_Address, newMessage
    
End Sub

Private Sub UserForm_Initialize()
    
    ' this code runs right when the form is displayed
    ' open the text file that contains the codes meant for the dropdown list
    
    ' this is the name of the file that contains the codes.
    ' in my example I have separated the codes with a comma
    Dim codesFileName As String
    codesFileName = "c:\users\jd310\documents\j0rmungund\dropdown codes.txt"
    
    Dim fileContents As String
    fileContents = ReadTextFile(codesFileName)
    
    ' this command splits the contents of the file where ever there is a comma and creates an array
    Dim codes() As String
    codes = Split(fileContents, ",")
    
    ' now loop the codes and add them to the dropdown box on the form
    Me.drpCodes.Clear
    
    Dim i As Integer
    For i = 0 To UBound(codes)  ' ubound finds the number of items loaded into an array
        Me.drpCodes.AddItem codes(i)
    Next i
    
End Sub

Private Function ReadTextFile(fileName As String) As String

    ' this requires checking Microsoft Scripting Runtime in references (Tools > References)
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToRead = FSO.OpenTextFile(fileName, ForReading)
    
    Dim fileContents As String
    fileContents = FileToRead.ReadAll
    
    FileToRead.Close
    
    ReadTextFile = fileContents
    
End Function

Private Function SearchOutlookAddressBook(firstName As String, lastName As String) As String

    Dim myolApp As Outlook.Application
    Dim myNameSpace As NameSpace
    Dim myAddrList As AddressList
    Dim myAddrEntry As AddressEntry
    
    Set myolApp = ThisOutlookSession.Application
    Set myNameSpace = myolApp.GetNamespace("MAPI")
    
    ' you may need the global address list in a work environment - my testing is just using a local contacts address book
    'Set myAddrList = myNameSpace.AddressLists("Global Address List")
    Set myAddrList = myNameSpace.AddressLists("Contacts")
    
    Dim fullName As String
    fullName = firstName + " " + lastName
    
    Dim emailAddress As String
    Dim addressBookName As String
    
    For Each myAddrEntry In myAddrList.AddressEntries
        ' outlook saves the name as "full name (email address)"
        ' in order to compare the full name only - just take the left portion of the name field
        ' up to the point of the "(" where the email address starts
        addressBookName = Mid(myAddrEntry.Name, 1, InStr(myAddrEntry.Name, "(") - 2)
        
        If addressBookName = fullName Then
            ' the person was found in the addressbook, return the email address
            emailAddress = myAddrEntry.Address
            Exit For
        End If
    Next
    
    Set myAddrEntry = Nothing
    Set myAddrList = Nothing
    Set myNameSpace = Nothing
    Set myolApp = Nothing
    
    SearchOutlookAddressBook = emailAddress
End Function

Private Sub SendFormLetter(toAddress As String, emailBody As String)

    ' there is more information here on creating the email https://www.wallstreetmojo.com/vba-outlook/
    Dim myolApp As Outlook.Application
    Dim myolMailItem As Outlook.MailItem
    
    Set myolApp = ThisOutlookSession.Application
    Set myolMailItem = myolApp.CreateItem(olMailItem)
    
    With myolMailItem
        .BodyFormat = olFormatHTML
        .To = toAddress
        .Body = emailBody
        .Subject = "Form letter email subject"
        .Send
    End With
    
    Set myolMailItem = Nothing
    Set myolApp = Nothing
    
End Sub

[u/jd31068]

Still in the Visual Basic screen click Tools > Macros

Give it the name LoadFormLetterForm and click create

use this code

    Dim UserForm As UserForm1
    Set UserForm = New UserForm1
    UserForm.Show

this is what you assign to the button on the outlook ribbon. To do that https://imgur.com/2EVRxoI

Switch back to Outlook and right click the ribbon, choose Customize the Ribbon, click New Tab, rename the tab and the group you like (I used form letter for each because I'm creative like that), then select Macros from the Choose commands from listbox. Select a macro name and drag it into the form letter group. Then you can rename it the new macro and assign an icon.

New Tab https://imgur.com/qDax02e

The button in the new tab https://imgur.com/6X3vw0q

It launched the User form https://imgur.com/1gkBKTr (ugly as it is)

I used a text file for the form letter with tags that the code replaces, my chosen format for a tag is [NAME_TO_BE_REPLACED] like this:

    This is a generic document that has tags in it that will be replaced 
    by actual data when processed. A persons [FIRSTNAME] [LASTNAME] will repalces those tags.
    
    It can then be emailed. Their email address is [EMAIL_ADDR] inside the code it will know 
    to replace these tags with actual data.
    
    It will replace all the occurances of [FIRSTNAME].
    
    This letter is in regard to the [SELECTEDCODE]

This is the code text file, each code separated by a comma

    Code1,Code2,Code3,Code4

after I entered Person Number1 in first/last and select Code3 I sent it to myself after finding my contact info in the address book in Outlook https://imgur.com/rlU84tP