Automating Microsoft Outlook
In this article, I would like to share my experience of automated mail processing and of automating the office routine task of sending messages to a group of clients.
So, in fact, what the question is: you need to send emails with an attachment to several dozen clients. In this case, there should be only one address in the recipient field, i.e. clients do not need to know about each other. In addition, installation of additional software such as MaxBulk Mailer and the like is not allowed. We only have Microsoft Office at our disposal, and in this particular case, Microsoft Office 2013.
I describe, in my opinion, the most variant – without the use of templates, drafts and formatting. For our purposes, we need Outlook (go to the VBA editor and add a module, we also include "Microsoft Excel 15.0 Object Library" in Tools > References), a text file with a list of recipients according to the "one line-one address" principle, a text file with the body of the letter and files to send as attachments.
The general algorithm is as follows: we specify the data for the fields and generate letters, looping through the recipients.
I note right away that this example is not some kind of perfected code that works with maximum efficiency with minimal size. But it works and copes with the declared functionality. Actually, I was just too lazy to send dozens of letters manually and I wrote this program, and then decided to share it. If someone is interested, he can improve the code to his heart's content.
VBA, by default, does not require a clear declaration of variables and their types. In principle, you can do without it altogether.
Therefore, some variables in "episodic roles" are not described in the construction with Dim.
So, first we request the subject of the letter with the implementation of the check for the cancellation of the action.
TxtSubj = InputBox("Topic", "Campaign") If Len(Trim(TxtSubj)) = 0 Then Exit Sub End If
Now it's the turn for files with addresses and the text of the letter. Here there is a nuance. How to call the file selection dialog? I don't even want to think about hard prescribing the path. So you have to come up with something. The variant used by many with Application.GetOpenFilename will not work, since there is no such method in Outlook. I tried using the API. The option with "Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll"…" did not work (PtrSafe due to the fact that the system is Win7, x64). Didn't throw any errors, but nothing came up when calling. I did not find a solution on the Internet. If anyone can suggest a solution, I'd be grateful. Thus, I had to go around using the Excel.Application object.
Dim xlApp As New Excel.Application Set fd = xlApp.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Email content" .Filters.Add "Text file", "*.txt", 1 If .Show = -1 Then For Each vrtSelectedItem In .SelectedItems Path2Body = vrtSelectedItem Next vrtSelectedItem Else Exit Sub End If End With Set fd = Nothing
And now the investment. Here I used a dynamic array and the ability to multiple-select a dialog.
Set fd = xlApp.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = True .Title = "Attachments" .Filters.Add "All files", "*.*", 1 If .Show = -1 Then i = 0 ReDim Preserve Path2Att(i) For Each vrtSelectedItem In .SelectedItems Path2Att(i) = vrtSelectedItem i = i + 1 ReDim Preserve Path2Att(i) Next vrtSelectedItem Else Exit Sub End If End With Set fd = Nothing
Set fd = xlApp.FileDialog(msoFileDialogFilePicker) With fd .AllowMultiSelect = False .Title = "Address list" .Filters.Add "Text file", "*.txt", 1 If .Show = -1 Then For Each vrtSelectedItem In .SelectedItems Path2To = vrtSelectedItem Next vrtSelectedItem Else Exit Sub End If End With Set fd = Nothing
Each time I created and deleted the fd object because it was easier to do than to clean it up before the next call.
To get data from text files, I had to use a couple of additional functions. They are called like this:
In this example, the ability to send simple letters is implemented. If you need to expand the possibilities, for example, make the text formatted, then you should move in the direction of Outlook.MailItem > GetInspector > WordEditor. This, to put it mildly, complicates the code, but will allow you to use a formatted Word document as the source of the text of the letter.
You can also add handling of the "deliberate" absence of any components of the letter. For example, implement sending without a subject, text, or attachments. Now the failure of one of these elements will lead to an interruption of the procedure.
This code, in theory, should also work in earlier versions of Microsoft Office. Only the link to the Excel library will change.