Outlook email body WordEditor

[EvilC]

Hello all,

I am adjusting a macro written by an ex colleague and im stuck with the following.

This macro selects a range and pastes it into and email using ActiveInspector.WordEditor. Now i cant seem to get the email text to appear before the range is pasted into the email body. I have used the Range.InsertBefore method but this does not work. Any help is much appriciated.

Sub email_reports_pt_res(autoSend As Boolean, strCC As String)
    
    Dim aOutlook As Object
    Dim aEmail As Object
    Dim time As Integer
    Dim emailText As String
    Dim emailSignature As String
    Dim wDoc As Object
    
    'Outlook objects initialization
    Set aOutlook = CreateObject("Outlook.Application")
    Set aEmail = aOutlook.CreateItem(0)
    aEmail.Display
    Set wDoc = aOutlook.ActiveInspector.WordEditor
    
    'Setting the signature
    emailSignature = "Best Regards, "
    emailText = vbCrLf & vbCrLf & vbCrLf & "Please find above information regarding Interval Report from " & Worksheets("Email Templates").Range("C21") & vbCrLf & vbCrLf & emailSignature
    
    'Setting the email properties
    aEmail.Importance = 1
    aEmail.Subject = "Email Dashboard "
    aEmail.To = "<email@email.com>; <email@email.com>"
    aEmail.CC = "<email@email.com>; <email@email.com>"
    
    
    'Signature bound
    wDoc.Range.InsertBefore (emailText)

    'Copy Dashboard
    Worksheets("Email Templates").Range("B2:AB34").CopyPicture Appearance:=xlScreen, Format:=xlPicture
    wDoc.Range.Paragraphs(1).Range.Paste
      
    
    'Resizing all the images for a fixed width and length
    For Each IShape In wDoc.InlineShapes
        IShape.LockAspectRatio = True
        IShape.Height = IShape.Height * 1.4
        IShape.Width = IShape.Width * 1.5
    Next IShape
    
   'Disabling the default signature
    DeleteSig aEmail
    
    'Sending Mode
    If (autoSend) Then
        'Autosend the email
        aEmail.Display
    End If
    
    'Clearing memory
    Set aOutlook = Nothing
    Set aEmail = Nothing
    Set wDoc = Nothing

End Sub

 

[GWteB]

In what way does it not work? Is the text displayed on an unexpected location within the email body? Are you getting an error message? If yes, what does it say? If no, what more info can you provide? Did you reference the Microsoft Word Object Library within your VBA Project?

 

[EvilC]

Hello GWteB. There is no error message, script works, but the email text does always seem to appear after the range is pasted as picture. So using

wDoc.Range.InsertBefore (emailText)

would suggest to past this before the "picture" paragraph. Not sure what you mean with referenced it within the VBA Project.

 

[GWteB]

So using wDoc.Range.InsertBefore (emailText) would suggest to past this before the "picture" paragraph.

The InsertBefore method just inserts something before something else that already exists.
The email body is empty or contains an automatic signature, so the containings of your emailText variable will be inserted at the end (of the empty body) or before a signature if present. In your code the worksheet range is copied afterwards.
Easiest way to construct an email body using VBA is from bottom to top. Manually design the content and order of the email body and then cut this design into manageable parts. Then place it with VBA in this order (from bottom to top) in the email body using the Paste and / or InsertBefore methods, for example:
1. signature
2. closing greeting
3. summary if applicable
4. graphic or image
5. introductory text
6. "Dear customer,"

Not sure what you mean with referenced it within the VBA Project.

It seems that with your current code no such reference is necessary, but I meant this: Menu> Tools> References