Formatted text from excel cell that I wish to use as body in email

[andycapp28]

Hi I need to know how or if its even possible to retain the formatting I have in a cell of text that I wish to use as the body of an email from outlook.

For example

Not replace where I have used alt CR with CR LF

Also any bold, colours etc.

 

[SkipVought]

Can you do what you want using copy 'n' paste?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

I have over 100 emails to do each month copy/pasteing is a tedious job.

The email addresses are held in an excel file that is processed via a vba macro you helped me develop.

However I now need to accomodate the company brand for formatting of an email, eg font, signature.

Tried storing it in a cell but all gets lost when outlook sends email.

If I could make my macro use a word doc, maybe that would be the answer.

 

[SkipVought]

I did not mean DO the entire thing with CNP.

I meant and you use the CNP method to get the data that you want into an eMail?

If that METHOD works for you, then it can be coded and replicated as many times as you like!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Apologies Skip for misunderstanding you.

CNP manually is how I do it now.

I just use an excel spreadsheet and macro to find the email addresses and file names to attach.

Any help you can offer then on how to automate the CNP bit (the body content of the email) is what I need and that it retains my Brand formatting.

Thanks
AC

 

[SkipVought]

I assume that you are coding in Excel. Do you have any code yet that instatiates the Outlook application object and manipulates the appropriate MAPI objects?

In any case. please post the relevent code you have.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

hi Skip,

Currently I use this.
Please excuse any commented out lines

Sub ESS_Mail()
'
'   Email all supported held in excel file and _
    attach his/her file from Monthly Query results
'
    Dim r As Range, c As Range
    Dim rLookfor As Range
    Dim rLookin As Range
             
    With Application
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    End With
    
    With Worksheets("ESS_Mail")
    Set rLookin = .Range(.Cells(2, "E"), .Cells(.UsedRange.Rows.Count, "E"))
    End With
    
    For Each r In rLookin
            
        If Trim(r.Value) <> "" Then
         ' Now Email and attach his/her data file
        
         ESubject = "ESS Report"
         ESendto = Replace(r.Value, "mailto:", "")
         EFilename = r.Offset(0, -4)
                 
         Set App = CreateObject("Outlook.Application")
         Set Itm = App.CreateItem(0)
        
            With Itm
            .Subject = ESubject
            .From = EFrom
            .To = ESendto
            ' Debug.Print EFilename
            '.CC = CCTo
            .Attachments.Add "G:\Marketing\Management and admin\Monthly Management Reports\ESS & EIT Monthly Reporting\FY 09-10\2010 03\ESS_Reports\" & EFilename
            .Display ' This property is used when you want _
              the user to see email and manually send.
            .Send
               
            End With
        
        Set App = Nothing
        Set Itm = Nothing
        
        End If
    Next

  With Application2
    Application.ScreenUpdating = True
    Application.EnableEvents = True
  End With

End Sub

 

[fumei]

I can no longer program in Outlook (as they have completely retricted VBA access to it - all macros are disabled as Policy). However, if I recall, normal format copying does not work programmatically. Yes, you can manually copy and paste retaining format, but you can not do so programmatically, retaining format. You can somewhat get around this by passing content into the mailitem body as HTML.

Gerry

 

[Gavona]

As a picture, in Excel 2003 at least.
Select the table.
Hold Shift as you click on Edit
Copy Picture.



Gavin

 

[Gavona]

oops, I meant to include the code

Range("A1:C8").CopyPicture Appearance:=xlScreen, Format:=xlPicture

Gavin

 

[fumei]

And how do you paste that into the MailItem object in Outlook?

Gerry

 

[Gavona]

Hi Gerry,
I admit I only pointed to a partial solution. I don't have outlook here to test but doesn't it depend on your choice of email editor?..

....Hmm. A bit of googling suggests this is a bigger issue than I realised.

This any good:

http://www.rondebruin.nl/mail/folder3/mail4.htm

or save as a GIF before attaching

http://www.codeforexcelandoutlook.com/blog/2009/04/export-excel-range-to-a-picture-file-redux/

Gavin

 

[fumei]

A bit of googling suggests this is a bigger issue than I realised."


Oh yeah....

An important point from that link:

This is not working if Word is your mail editor in Outlook 2000-2003

Issue #1

Perhaps the make-an-image-and-insert method is acceptable to the OP, but often this is not. People want the formatted text, not an image.

Gerry