![]() Split() is used to create a string array based on a delimiter like vbCrLF, or a quote mark, or such. It is not fool-proof but could have been used in this sort of task where you might have wanted to keep the prefix or suffix parts of a string for example. It simply replaces the first string delimited by quote marks. The ReplaceFirstString() function not used in the code but included since I thought it might be of use sometime. Once you have the index value, you know which element of the string array you are going to replace. If string is not found, -1 is returned which tells you that no string was found in any of array element's values. Pass that value as vbBinaråompare if you want case sensitive searches in the Instr() method. The 4th and last optional parameter is default value to not be case sensitive. ![]() If True, it looks for the string of letters from the left. The 3rd parameter is optional and set to default value of false, look for string any where in each array element's value. The 2nd parameter is the string array to search. The first parameter is the string to find. I normally use WorksheetFunction.Match to search an array but made this routine which seemed to work better and had more versatility. The function IndexInArray() returns the index number of the string array that has the string that was found or not (-1). The value of img is the string that you want to use to replace the first string in the HTM file that has the string, "ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) Set fso = CreateObject("Scripting.FileSystemObject") If UBound(s) >= 1 Then s(1) = replaceStringįunction GetBoiler(ByVal sFile As String) As String Optional tfMatchLeft As Boolean = False, Optional cm As Integer = vbTextCompare) As LongÄim pos As Long, i As Long, ii As Integerįor i = LBound(anArray) To UBound(anArray)įunction ReplaceFirstString(aString As String, replaceString As String) As String Set cm=vbBinaråompare for exact string comparison.įunction IndexInArray(aValue As String, anArray() As String, _ 'for a found string in the first element matched. 'Show full array element value in Immediate Window 'Change only Mysig.htm to the name of your signature "Please visit this website to download the new version." & _ Strbody = "Dear Customer Ron de Bruin" & _ Set OutApp = CreateObject("Outlook.Application") ' Don't forget to copy the function GetBoiler in the module.Äim img As String, s() As String, i As Long Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function ' Working in Office 2000-2013 Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim SigString As String Dim Signature As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strbody = "Please visit this website to download the new version." SigString = Environ("appdata") & "\Microsoft\Signatures\MySignature.htm" If Dir(SigString) "" Then Signature = GetBoiler(SigString) Else Signature = "" End If On Error Resume Next With OutMail. ' Don't forget to copy the function GetBoiler in the module. The question is thus how, if at all possible, can I use something like Environ("appdata") instead of C:\Users\vanhunk\AppData\Roaming in the MySignature.htm file? My aim is to be able for anyone using the workbook to send e-mails to have their own default e-mail signature displayed, without having to display the e-mail in order to get the default signature. I had to change the following line in the MySignature.htm file: ![]() In order for the code to work properly I had to change a line in the MySignature.htm file (found method on the internet, but canât remember the source). I used the code be Ron de Bruin and modified it slightly to be able to include a custom signature with a picture. How can Environ("appdata") be incorporated in the MySignature.htm file? I used the method below to achieve this with a custom signature, but could not do it with the default signature. I need a method to send Outlook e-mails from Excel, without having to display the e-mail to retrieve the default signature:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |