Outlook email body WordEditor

EvilC

Board Regular
Joined
Jul 26, 2014
Messages
74
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,103
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jul 26, 2014
Messages
74
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
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,103
Office Version
  1. 2013
Platform
  1. Windows
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

ScreenShot134.png
 

Watch MrExcel Video

Forum statistics

Threads
1,114,279
Messages
5,546,943
Members
410,764
Latest member
Dedeke
Top