MS Office and VBA

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

If you want to export a collection of type Scripting.Dictionary into a text file the below function may come handy.

The below method will replace all special or escape characters in a provided String.

This is of use, if you have to use before unknown Strings from any source for example in a file name.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
' @Author - Alexander Bolte
' @Description - replaces all characters, which match the pattern "[^A-Za-z0-9-_ ]" in a given String.
' @Param aText - a String in which invalid characters should be replaced.
' Returns the handed String from aText excluding all replaced characters.
Public Function replaceSpecialCharacters(ByVal aText As String) As String
    Dim regEx As New RegExp
    Const cReplEscapeChar As String = "[^A-Za-z0-9-_ ]"
    
    regEx.pattern = cReplEscapeChar
    regEx.Global = True
    regEx.IgnoreCase = True
    aText = regEx.Replace(aText, "")
    
    replaceSpecialCharacters = aText
End Function

Enjoy. 

If you have to create and send an email automatically, the following method might help.

I have not tested it with HTML yet, but will do that at some point.

The method requires MS Outlook to be installed, since it is automating Outlook  in order to create an email with attachment.