Simple email templates in MS Outlook VBA
A small recipe on MS VB for system administrators who want to make life easier for employees.
Task: the girl at the reception periodically sends official announcements to the entire company. At the same time, there is a design drawn by the designer, and the sending process is able to “find the old letter in the sent ones, press send to all, edit the text and subject of the letter, send”.
There are two ways:
Templates - suitable for simple options when all fields are static
VBA - under the cut, consider the example of creating the simplest template on the built-in Outlook Visual Basic.
')
Of course, there is also a normal way - to take a programmer who will write an automatic mailing of letters on any convenient engine, get data about employees from Exchange or LDAP and everything will be ok, but for this you need a budget. Who cares a crutch - under cat.
The VBA macro can be configured to automatically fill in all fields fairly flexibly. You can add a macro call directly to the Outlook ribbon by creating an icon for it.
The user can accidentally delete a file with a template, or bungle it. In such cases, the macro is more reliable, and always on the panel of quick buttons.
The layout of the letter in Outlook goes to HTML, and below - the instruction and an example of a simple code that creates a letter, attaches two pictures with logos, formats the html-body of the letter, prescribes letter fields.
So:
How can you improve?
You can add several inputboxes, or a form with a radio button, where you can select some parameters by selecting either recipients or the desired type of announcement.
You can do a search in the outlook address book (a cycle in Outlook.Application.GetNamespace ("MAPI"). AddressLists ("Global Address List"). AddressEntries), and use information from the card (name, project, contact details) to create personalized letters .
You can make several blanks for different fields, and select the desired randomly or by date / time.
Unfortunately, I’ve come across the fact that if you view html emails not in desktop outlook, you may have problems
Even for some reason, even the native exchange web-interface can bite off parts that it considers superfluous (for me it did not display pictures in the table header, although it displayed pictures of employees in the same table). I could not figure out the principle on which he chose what to display or not - all files were attacked equally.
A client with a poppy sees a design that has gone down, there is even worse in the web interface.
The reason is that exchange reworks all html-code and displays it according to its own personal rules, using creepy cpan instead of what I designed (div / table ...). Attempts to google the decision while sad - apparently in the current exchange it can not be done. If you had successful solutions - share!
PS Yes, this is of course a crutch, but in my company it saved about 200 man-hours a year, which is quite a lot.
Task: the girl at the reception periodically sends official announcements to the entire company. At the same time, there is a design drawn by the designer, and the sending process is able to “find the old letter in the sent ones, press send to all, edit the text and subject of the letter, send”.
There are two ways:
Templates - suitable for simple options when all fields are static
VBA - under the cut, consider the example of creating the simplest template on the built-in Outlook Visual Basic.
')
Of course, there is also a normal way - to take a programmer who will write an automatic mailing of letters on any convenient engine, get data about employees from Exchange or LDAP and everything will be ok, but for this you need a budget. Who cares a crutch - under cat.
The VBA macro can be configured to automatically fill in all fields fairly flexibly. You can add a macro call directly to the Outlook ribbon by creating an icon for it.
The user can accidentally delete a file with a template, or bungle it. In such cases, the macro is more reliable, and always on the panel of quick buttons.
The layout of the letter in Outlook goes to HTML, and below - the instruction and an example of a simple code that creates a letter, attaches two pictures with logos, formats the html-body of the letter, prescribes letter fields.
So:
- Run Macro Editor in Outlook ( Alt + F11 )
- Create a new module
- Write the code
Attribute VB_Name = "Announce" Option Explicit Sub CreateAnnounce() Dim htmlBody As String Dim fileAttach As Outlook.Attachment Dim newMail As Outlook.MailItem Set newMail = Application.CreateItem(olMailItem) ' , html- src="cid:< >" ' (position) 0, " " Set fileAttach = newMail.Attachments.Add("c:\images\logo1.png", , 0) Set fileAttach = newMail.Attachments.Add("c:\images\logo2.png", , 0) Set fileAttach = Nothing // ' . , - , . . htmlBody = "<table align=center style='width:650;border:solid #316AA5 6.0pt;background=white;padding:0'>" + _ "<tr height=150>" + _ "<td style='padding:20;width:200' valign=top><img src='cid:logo1.png' height=150></td>" + _ "<td style='padding:20;width:200' align=right valign=top><img src='cid:logo2.png' height=150></td></tr>" + _ "<tr><td colspan=2 valign=top style='padding:80'> <h3> , </h3><br><br>" + _ "SCIgen — , , , , . : « , »." + _ "<br><br> .<br><br><hr> , <br><br></tr></table>" With newMail .Subject = "[Announce] " + Format(Date, "dd.mm.yyyy, dddd") .To = "test@example.com" .BodyFormat = olFormatHTML .htmlBody = htmlBody .Display End With Set newMail = Nothing End Sub - 4. Add a call to this macro in the Outlook feed.
- 5. Profit
How can you improve?
You can add several inputboxes, or a form with a radio button, where you can select some parameters by selecting either recipients or the desired type of announcement.
You can do a search in the outlook address book (a cycle in Outlook.Application.GetNamespace ("MAPI"). AddressLists ("Global Address List"). AddressEntries), and use information from the card (name, project, contact details) to create personalized letters .
You can make several blanks for different fields, and select the desired randomly or by date / time.
Unfortunately, I’ve come across the fact that if you view html emails not in desktop outlook, you may have problems
Even for some reason, even the native exchange web-interface can bite off parts that it considers superfluous (for me it did not display pictures in the table header, although it displayed pictures of employees in the same table). I could not figure out the principle on which he chose what to display or not - all files were attacked equally.
A client with a poppy sees a design that has gone down, there is even worse in the web interface.
The reason is that exchange reworks all html-code and displays it according to its own personal rules, using creepy cpan instead of what I designed (div / table ...). Attempts to google the decision while sad - apparently in the current exchange it can not be done. If you had successful solutions - share!
PS Yes, this is of course a crutch, but in my company it saved about 200 man-hours a year, which is quite a lot.
Source: https://habr.com/ru/post/249947/
All Articles