Word to Outlook, utilising a UserForm prior to sending email

[HTSCF Fareha]

Having already posted in this section with an Excel to Outlook project, which unfortunately worked great at home, but refused to do the business at work, I’m now thinking of "adapting" it to go from Word to Outlook.


The idea is that this will also give an option to edit / tweak the text via a UserForm before committing to an email, utilising Ron de Bruin’s Outlook bridging script in the process.


I do not know if it is possible for the main TextBox to update on the UserForm when the ComboBox and two other TextBoxes are updated by the user?


I’d really appreciate some help in trying to realise my ambition.

[gmayor]

The attached will work from Word (and from Excel with very little modification).
Run the code from the Main module.
As you are creating the message directly from the Userform, the content control references are superfluous, and you shouldn't need the Sleep code using this method of opening Outlook.

[HTSCF Fareha]

Many thanks, Graham.

Makes sense that the CC is superfluous as there is nothing being written to the Word document itself.

I've got the Main module to open the UserForm, but noticed that only the "Officer" was being inserted. After adding the "Force" to the predefined text, I've realised that the Replace function will only update one of the two and not both.

This doesn't work

sMessage = Replace(.txtEditor.Text, "[Force]", sForce)
sMessage = Replace(.txtEditor.Text, "[Officer]", sOfficer)

But I cannot find anything online on how to replace two or more words using the Replace function, so am guessing that a different approach might be required?

[gmayor]

You are right

sMessage = Replace(.txtEditor.Text, "[Force]", sForce)
sMessage = Replace(.txtEditor.Text, "[Officer]", sOfficer)

does not work however

sMessage = Replace(.txtEditor.Text, "[Force]", sForce)
sMessage = Replace(sMessage, "[Officer]", sOfficer)

should.

[HTSCF Fareha]

Thanks Graham, this does indeed work!

I guess it is changing the focus of what needs to be looked at in order for a second selection to be altered?

Just for a further bit of understanding, if for example I had another case (or two) that needed updating within the email text, would it then mean that a different approach would be required, or could your method be pushed further?

[gmayor]

The same method could be pushed further.

sMessage = Replace(sMessage, "[Keyword]", Value)

[HTSCF Fareha]

That's really interesting and useful to know, thank you.

Obviously I tried changing

sMessage = Replace(.txtEditor.Text, "[Force]", sForce)

for

sMessage = Replace(sMessage, "[Force]", sForce)

and then adding another replacement to test. Although it wouldn't play ball unless the original

sMessage = Replace(.txtEditor.Text, "[Force]", sForce)

was put back in the first replacement position.

[gmayor]

It might have been clearer had I added an extra step at the start

sMessage = .txtEditor.Text

[HTSCF Fareha]

That works for me, thanks Graham.


Okay, back to the work environment and I'm getting the dreaded Run-time error '287': Application-defined or object-defined error on

Set wdDoc = olInsp.WordEditor

To cover some checks made:-

Outlook (Office 2016) is the default and only email application at work.
The vba editor > tools > references on both home and work PCs match.
Both versions of work and home Office are 32 bit.

references.jpg

The only thing that might help pinpoint the issue is that the home setup, which I have three email accounts active in, seems to generate the email in the 'My Outlook Data File' section, which looking at the work setup does not exist. I really cannot see any other differences.

[gmayor]

The attached works correctly here. I cannot say why it is not working for you. The code uses the default Outlook e-mail account.

[HTSCF Fareha]

Yes, that's exactly how I have it, with the slight alteration to allow for post #8 as follows.

sMessage = .txtEditor.Text
        sRMSnumber = .txtRMSNum.Text
        sForce = .cboForce.Value
        sOfficer = .txtOfficer.Value
        sMessage = Replace(sMessage, "[Force]", sForce)
        sMessage = Replace(sMessage, "[Officer]", sOfficer)

Totally baffling!

I'm not even sure what I could ask our IT department. It's the only thing that I can think that there must be something that they have in place to stop office apps interacting with each other for some reason.

[HTSCF Fareha]

I added an 'On Error Resume Next' line as per the attached and this allowed an email to be generated with a recipient, from and subject line, but nothing else. Not sure if this helps provide any clues as to what might be going on?

[gmayor]

The On Error line would cause the macro to skip the use of the Word editor, which is where the error occurs, hence the absence of information in the message.
The problem is that the errant system has a communication issue between Outlook and Word, but why that should be so, I cannot say. It could be attributable to security issues or simply that Office needs repairing. You could try (if permitted) to run the following Outlook macro to see if Outlook can access Word.

Sub Testlink()
Dim olEmail As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim oLink As Object
Dim strLink As String
Dim strLinkText As String
    'The texts before and after the link
Const strText1 As String = "If you wish to view or download any of my Office add-ins, please simply follow this link: " & vbCr & vbCr
Const strText2 As String = vbCr & vbCr & "Should you require support for any of those products, please use the web site contact link."

strLink = "https://www.gmayor.com/Word_pages.htm"    ' the link address
strLinkText = "Click here for Graham Mayor's Web Site "    ' the link display text
strLink = "https://www.gmayor.com/Word_pages.htm"    ' the link address

    On Error Resume Next
    Set olEmail = CreateItem(olMailItem)
    With olEmail
        .BodyFormat = olFormatHTML
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range(0, 0)
        oRng.Text = strText1
        oRng.collapse 0
        Set oLink = wdDoc.hyperlinks.Add(Anchor:=oRng, _
                                         Address:=strLink, _
                                         SubAddress:="", _
                                         ScreenTip:="", _
                                         TextToDisplay:=strLinkText)
        Set oRng = oLink.Range
        oRng.collapse 0
        oRng.Text = strText2
        .Display
    End With
lbl_Exit:
    Set oLink = Nothing
    Set oRng = Nothing
    Set wdDoc = Nothing
    Set olInsp = Nothing
    Set olEmail = Nothing
    Exit Sub
End Sub

If not, this is something you need to address with your IT support.

[HTSCF Fareha]

Thanks, Graham.

I could run the code in the previous post okay in Outlook which produced a blank email with some text and a link back to your website.

So I have made contact with our IT team who have suggested that this is "likely" to be a case of getting it digitally signed. I've made the request and will now have to wait until they get around to it.

Fingers crossed!

[gmayor]

That could be it. You could self certify on the errant PC to test the premise, but it won't work for others.
https://www.gmayor.com/create_and_em...gital_cert.htm

[HTSCF Fareha]

Have had a long chat with the IT department today who have tried to get the digital signing sorted. They created the signature okay, but couldn't get it to register with the template. Apparently we have been having some "integration updating" issues that are causing some issues. Unknown at this stage when this might be fixed, but apparently it is affecting a number of users so hopefully not too long.

[HTSCF Fareha]

Okay, so the Digital Signatire has finally been applied and alas, I'm still getting the dreaded Run-time error '287': Application-defined or object-defined error on

Set wdDoc = olInsp.WordEditor