CODEWITHSUNDEEP
excelvbaoutlookms-word
Bassta
Bassta

Reputation: 53

Replacing text in an email with a picture from the clipboard

I have a report that I send out daily.
In it there is a linked image to a range that I need to paste into an email and send.
I have the email saved as an outlook template file that I modify as necessary when I need to.
The code I have runs from Excel.
I create an Outlook object and open the Outlook email from the .oft template file, then I want to find the identifier text (INSERT IMAGE HERE) and replace it with the linked image from Excel, which has been copied from the clipboard. I am trying to add it as an InLineShape so that it behaves properly in the body of the email.
I have been attempting to do it with a defined WordEditor from the body of the email.
I looked into HTML to do it, but I decided the WordEditor object would be a better approach. After I paste it, I need to set the scale height to 0.75 so that it is displayed properly.
From there, just click send.

Here is a quick sample of my code:

Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookEmail = OutlookApp.CreateItemFromTemplate(templatePath) 
OutlookEmail.Display 'Just for code checking
ThisWorkbook.Worksheets("Email Sheet").Pictures("Summary Email Screenshot").Copy 
Set EmailText = OutlookEmail.GetInspector.WordEditor 
With EmailText.Range.Find 
    .ClearFormatting
    .Execute findText:="*INSERT IMAGE HERE*", MatchWholeWord:=True, MatchCase:=True, _
        MatchWildcards:=False, ReplaceWith:="^c", Replace:=wdReplaceOne
End With

EmailText.InlineShapes(EmailText.InlineShapes.count).ScaleHeight = 75 
'OutlookEmail.Send

The problem arises with that last line before I send it.
What happens is it is not inserted as an InLineShape, but just a shape so that it floats on top of the text.
I need it as an InLineShape so that the content after the image moves down and is displayed rather than being covered by it. I've been scowering forums for a while to try to find an answer but to no avail.
I am pretty well versed in Excel VBA, but this is my first attempt at Outlook and Word VBA so please, bear with me.

Upvotes: 4

Views: 498

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

By default when an image is pasted Word will use the setting Insert/paste pictures as from File/Options/Advanced, section "Cut, copy and paste". So on some machines an image might be pasted as an InlineShape and on others as a Shape.

If you were simply pasting in the code then using PasteSpecial it would be possible to specify inline or with text wrap using the corresponding WdOLEPlacement enumeration value of either wdFloatOverText or wdInLine.

Since the paste command is coming from the ^c replacement text this approach won't work for this situation. That means the Options setting will need to be changed. And, in order for this to be user-friendly, at the end of the code it should be changed back.

Here's some sample code demonstrating how to check the current option, change it if necessary, and reset at the end of the procedure. I'm not familiar with using Word through the Outlook object model, but I think I've correctly instantiated an object for the Word.Application so that VBA executing in Excel or Outlook will know what Options are meant. Except for the last line, this should come between Set and With in the code sample in the question.

Dim wrapType As Long
Dim wordApp as Object

Set wordApp = EMailText.Application
wrapType = wordApp.Options.PictureWrapType
If wrapType <> wdWrapMergeInline Then
    wordApp.Options.PictureWrapType = wdWrapMergeInline
End If
'Do other things, then reset

wordApp.Options.PictureWrapType = wrapType

Upvotes: 2

Related Questions