Wednesday, March 10, 2010

VBA Macro for Outlook to Save E-mails to File

This is hardly stupendous, and I found various versions of this on the Internet, but sadly, most of the versions simply did not work. The following was my final "massaged" version that actually worked pretty well. Not perfect, but it worked.

The task was to write a macro for Outlook that would save the e-mail as a straight TXT file, using the subject as the filename.

For example, if I got an email with the subject: This is my agenda
It would save that e-mail in a file as: This is my agenda.txt

There are some characters, however, that people insert into subject lines that prevent the file from being saved. For example: \, /, *, |

So I had to strip those out before saving the file.

Here is the final macro:

Sub save_email(myItem As Outlook.MailItem)
   Dim tempstr As String
   Dim strname As String
   Dim Badchar As String
   Badchar = "\/:*?<>()"  
   Dim intIndex As Integer
'pick up the e-mail subject

   tempstr = myItem.Subject
'Remove special chars

   For i = 1 To Len(tempstr)
     If InStr(1, Badchar, Mid(tempstr, i, 1), vbTextCompare) Then
        strname = strname & ""
        strname = strname & Mid(tempstr, i, 1)
     End If
'Save to a file

myItem.SaveAs "c:\Users\tuser\My Documents\" & strname & ".txt", olTXT
End Sub

That's it. The piece that was missing from most of the things I've seen posted on the Internet was the strname = strname & "" line where it actually removes the bad char and keeps on building strname with the characters from the e-mail's subject. I have no idea why other people's programs worked when you only specified strname = strname  but the script failed to remove the special characters when I did that, so I had to add the & "" part which essentially adds nothing to the string.

Go figure.
At least it seems to work.

1 comment:

Jazz Lee said...

I pasted your complete VBA code and combine a rule. The only one line of code I modified is about the path(myItem.SaveAs "E:\TEST\" & strname & ".txt", olTXT).But it didn't work. I don't know why. It just pop up a window and it's too fast I couldn't read the information. Is there any way to check what's going on?