Category Archives: MS Access – Outlook Automation

Using VBA to List Active Processes

Because of some recent work I’ve been doing trying to embed applications within an Access form (so far I’ve managed to embed Brave, Edge, PowerShell ISE, Excel, Word, …) I’ve been working with listing the active computer processes. So I thought I’d share a few procedures I’ve developed to aid me with that task.

I tried a couple different approaches to get the process listing.

Initially, I was using the cmd:

tasklist /fo csv /nh

and although it did work, it was slow and created unnecessary lag in my process (no pun intended). Eventually I turned to my tried, tested and true friend WMI!
 
Continue reading →

Find Outlook Appointments

After my previous post

Find and Save Outlook E-mails to Disk as MSG files
I thought I’d share some code to search/filter an Outlook folder to find specific e-mail items and then save them as individual MSG files on the hard drive (location of your choosing) The code presented below is building upon my article in which I explain how you can build search criteria to find specific items within Outlook.

I decided to continue development and hence today I present to you an approach to searching for appointments. The beauty here is once you implement this type of framework you can then easily:

  • Find Outlook Appointment and Save them to disk
  • Find Outlook Appointment and Display them to the user for their interaction
  • Find Outlook Appointment and Update them and then save the changes
  • Find Outlook Appointment and Delete them.
  • You could retrieve the items to display in a form for user interaction
  • …

Furthermore, such a Framework can be applied to just about any Outlook Items:

  • Mail
  • Contacts
  • Appointments
  • …

Continue reading →

Find and Save Outlook E-mails to Disk as MSG files

I thought I’d share some code to search/filter an Outlook folder to find specific e-mail items and then save them as individual MSG files on the hard drive (location of your choosing)

The code presented below is building upon my article

VBA - Search Outlook Emails/Items
I was asked, on this blog, about how one could search the inbox for e-mails from a specific sender with a term in the subject and thought this could be a good post to help others rather than leaving the answer buried on a random post page as a comment. The Key Concept The important thing to know is that Outlook has the Restrict Method which enables us to perform searches and retrieve just what we are looking for. Once you are aware of this, then it’s just a question of writing some code! (luckily for you, I’ve done that!)

in which I explain how you can build search criteria to find specific items within Outlook.

Continue reading →

VBA – Retrieve Outlook Signature File

I’ve been doing some work with Outlook recently and seeing postings to read signature files that have hard codes file names

sSig = CreateObject("Scripting.FileSystemObject").GetFile(Environ("appdata") & "\Microsoft\Signatures\MySignatureFile.htm").OpenAsTextStream(1, -2).ReadAll

Now this is great and all, but that means you need to know the name of the file and update it for each user, update it should they ever create a new one … a nightmare to manage!

So I set out to find a better way!

The Nightmare That Is Outlook

I’m not going to go on and on about it, but needless to say, Microsoft sure did not make thing easy for us developers and documentation about certain aspects appears, at least to me, to be non-existent. So it took some serious investigative work to figure out which registry keys were involved, … I so wish that Microsoft would standardize and cleanup their object models and gives us some desperately needed methods and properties, but if they haven’t by now I doubt I’ll ever see it since now all their energies are on the cloud!

The fact that even with the object available to me, I still have to go through the registry to get information is just mind blowing in 2020!

Moving on now.

The Solution

The solution requires a few procedures:

  • Determine the version of Outlook the user has so we can look things up in the registry
  • Lookup values in the registry
  • Encode string as proper HTML
  • Read the signature

Continue reading →

VBA – Send HTML Emails Using Outlook Automation – Improved

One of my most popular posts is my function for sending e-mail using Outlook automation: VBA – Send HTML Emails Using Outlook Automation. That said, there is an issue with that code, the way I retain the signature in the generate e-mail.

The Problem With Concatenation of .HTMLBody

Although this is the most common approach seen online (from what I have seen over the years); to concatenate the message we want to send with the existing body when the e-mail is first created:

.HTMLBody = sMyHTMLMessage & .HTMLBody

The issue being that .HTMLBody will already be a complete HTML document

<html  ...>
<head>...</head>
<body ...>
...
</body>
</html>

and the concatenation is prepending the user’s message at the beginning, outside the html/body tags, resulting in

The User's Message is prepended here!
<html  ...>
<head>...</head>
<body ...>
...
</body>
</html>

which is simply incorrect HTML!

Now, some e-mail clients, such as Outlook, deal with this just fine, but this can cause issue for others. As such, I wanted to find a way to generate an HTML e-mail while creating proper HTML.

Continue reading →

VBA – Search Outlook Emails/Items

I was asked, on this blog, about how one could search the inbox for e-mails from a specific sender with a term in the subject and thought this could be a good post to help others rather than leaving the answer buried on a random post page as a comment.

The Key Concept

The important thing to know is that Outlook has the Restrict Method which enables us to perform searches and retrieve just what we are looking for. Once you are aware of this, then it’s just a question of writing some code! (luckily for you, I’ve done that!)

Continue reading →

VBA – Extract Outlook Contacts

contact information

Once again, helping out with a forum question regarding the very limited information returned when using the External Data -> Import & Link -> More -> Outlook Folder. So, as per the usual, VBA give you the power to do it all and that is true when interacting with Outlook and Outlook Contacts. Below is the beginning of a procedure to extract any and all information from the Contacts folder.

Continue reading →