VBA Update the body from a template email

[hhj8810]

Hi, I'm new here. I have a problem when creating a new outlook email from a template(.oft or .msg), hope somebody can help.
The code as below, I don't know why the line ".HTMLBody = Replace(weeklyMSG.HTMLBody, "Index1", "Data1")" always report an error 287, but the .subject can be replace.
Does anybody know why that happens? Thanks a lot.

Sub MailTemplate()

    Set FileDialogObject = Application.FileDialog(msoFileDialogFilePicker)

    With FileDialogObject
        .Title = "Please select the template "
        .InitialFileName = ThisWorkbook.Path & "\"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Outlooktemplate Files", "*.oft, .msg"
        If .Show Then
            Dim weeklyMSGName As String
            weeklyMSGName = .SelectedItems.Item(1)
        End If
    End With

'Update the tempalte email

    Dim OLKapp As Object
    Set OLKapp = CreateObject("Outlook.Application")

    Dim weeklyMSG As Outlook.MailItem
    Set weeklyMSG = OLKapp.CreateItemFromTemplate(weeklyMSGName)
    weeklyMSG.Display

    'Dim xbody As String
    'xbody = weeklyMSG.HTMLBody

    With weeklyMSG
        .Subject = Replace(.Subject, "Review", "Test")
        .HTMLBody = Replace(weeklyMSG.HTMLBody, "Index1", "Data1")
    End With

    Set weeklyMSG = Nothing
    Set OLKapp = Nothing

End Sub

 

[hhj8810]

In that case, it could be a security issue (I could be wrong about this). Which MS Office version are you using?

MS Office 365, corporate version. Do you know where to check the security setting?

 

[Siddharth Rout]

MS Office 365, corporate version. Do you know where to check the security setting?

I am not sure where the MS Office 365 Outlook settings are. In the older versions it was at

HKEY_CURRENT_USER\Software\Policies\Microsoft\office\XX.XX\outlook\security\

Having said that, I tried your code and it works just fine. Where is the .oft file? Exchange or Local? I have a feeling it is in Exchange... Also try adding a Watch on .HTMLBody and see what do you get.

 

[hhj8810]

I am not sure where the MS Office 365 Outlook settings are. In the older versions it was at

HKEY_CURRENT_USER\Software\Policies\Microsoft\office\XX.XX\outlook\security\

Having said that, I tried your code and it works just fine. Where is the .oft file? Exchange or Local? I have a feeling it is in Exchange... Also try adding a Watch on .HTMLBody and see what do you get.

If it's the register that needs to be changed, then I don't have the authority on the corporate computer to change that.
The template file is a local file, just an outlook email and been saved as .oft.

By the way, If I cannot use this way to replace the data due to security issue which I cannot help with, do you have any other suggestion to achieve this?

 

[Siddharth Rout]

don't have the authority on the corporate computer

Then my suspicion was right. Exchange Server is involved and the probability of security issue is high.

In such a case, instead of using oft, can you not recreate the entire email? I mean, you will have to write the code just once?

 

[hhj8810]

Then my suspicion was right. Exchange Server is involved and the probability of security issue is high.

In such a case, instead of using oft, can you not recreate the entire email? I mean, you will have to write the code just once?

It should be a weekly email report which has a long body text, and i just need to update the numbers every week. If I don't use a template, what do you suggest to do to input such long body text every week, except compile all that into HTML code, that would be too long...

 

[Siddharth Rout]

It should be a weekly email report which has a long body text, and i just need to update the numbers every week. If I don't use a template, what do you suggest to do to input such long body text every week, except compile all that into HTML code, that would be too long...

Store the html body in a text file?

Something like (Untested)

Dim MyData As String, strData() As String

'~~> Change path as applicable
Open "C:\MyHTMLBodyText.Txt" For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1

With weeklyMSG
    .Subject = Replace(.Subject, "Review", "Test")
    .HTMLBody = Replace(MyData, "Index1", "Data1")
End With

BTW, I am assuming that .HTMLBody in the Replace is giving the issue. If it gives you the same error for the above code then we will have to completely take a different approach.

 

[hhj8810]

Thanks very much for your reply. It's too difficult to compile the body text to HTML code, it contains font colors, font sizes and tables, will it work if I save it as a draft in outlook?

 

[hhj8810]

Store the html body in a text file?

Something like (Untested)

Dim MyData As String, strData() As String

'~~> Change path as applicable
Open "C:\MyHTMLBodyText.Txt" For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1

With weeklyMSG
    .Subject = Replace(.Subject, "Review", "Test")
    .HTMLBody = Replace(MyData, "Index1", "Data1")
End With

BTW, I am assuming that .HTMLBody in the Replace is giving the issue. If it gives you the same error for the above code then we will have to completely take a different approach.

Thanks very much for your reply. It's too difficult to compile the body text to HTML code, it contains font colors, font sizes and tables, will it work if I save it as a draft in outlook?

 

[Siddharth Rout]

Thanks very much for your reply. It's too difficult to compile the body text to HTML code, it contains font colors, font sizes and tables, will it work if I save it as a draft in outlook?

Honestly, I have not worked with Exchange for a long time so I am not sure. You can give it a try. You could also use a Word Document instead of using oft?

 

[hhj8810]

Honestly, I have not worked with Exchange for a long time so I am not sure. You can give it a try. You could also use a Word Document instead of using oft?

thanks, I think I will work with WORD and then manually copy to email.