Add an Attachment Using an Array and Match first 17 Letters to Matching Template .oft to Send eMail


New Member
Outlook version
Outlook 2010 64 bit
Email Account
I've been trying to adapt Diane's code under "Use an Outlook Macro to Send Files by Email" using her array feature, but that array is fixed. I wish to change that fixed array into a variable array, much like the thread "Attachments to New email item" dated Oct 15, 2013. My ultimate goal is to run this as a macro or VBA once a month whereby the code would scan the invoice directory and for every unique invoice (there might be 15 or so), open a separate email using a pre-existing template (.oft) with the same 17 first letters and attach that invoice file and then display it. For example "P010213252 (FC42) YYYYYYYY.pdf" invoice file, then open the corresponding .oft template "P010213252 (FC42) XXXXX.oft". I'm using Outlook 16 under Win 10. I can get the fixed array to work to a certain extent, but the filename attached to each template is always the first .pdf file. I presume I need a second array for the .oft directory. Is there code available that can compare the two directories, one holding the invoices.pdf and the other holding the templates.oft, and if a match is found with the first 17 characters, then open that template .oft and load the matching .pdf file? I've searched but cannot find it. If it exists, please let me know. Thx.

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Because both are files, I think you could get a file name in one folder, look for a match in the other - it would be slower than reading both and creating arrays to compare.

This should read the folder and create an array - (untested, so i might have a typo or mistake) - then use each entry to check the other folder..
Dim FileDir As String
Dim strFiles
FileDir = "K:\Files\"
TemplateDir = "K:\Templates\"
strFiles = Dir(FileDir, vbDirectory)
While strFiles <> ""
If strFiles <> "." And strFiles <> ".." And Right(strFiles, 4) = "docx" Then
strList = Left(strFiles, Len(strFiles) - 5) & "." & strlist
End If
Dim myArray() As String
'Use Split function to return a zero based one dimensional array.
myArray = Split(strList, ",")

or if the template names all use the same format - create a string with the template folder names and if the value in in the arrary is in the string, use it to create the tempate name.

For i = LBound(myArray) To UBound(myArray)
If InStr(LCase(otherFolderList), myArray(i)) Then
templatepath = TemplateDir & myArray(i) & ".dotx"

' do whatever

Next i

Using Arrays in Outlook macros

Attachments to New email item