Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Add Cell Range as Picture to Email

Status
Not open for further replies.
LGMan

LGMan

MIS
Aug 27, 2003
233
GB
Hi, I want to automate a daily process of creating a new email, typing subjects and body text and adding an attachment, which I've been able to achieve. However I also copy a range of cells (C11 to O14) and paste into the email body as a picture, and this is where I am struggling.
Here's my code
Code: 
Sub Send_Email()
Dim thisWb As Workbook
Set thisWb = ActiveWorkbook
Dim Fname As String, strbody As String, Drng As String, Email_Attach As String, _
Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String, email_body2 As String
Dim Mail_Object, Mail_Single As Variant
Dim rng

Set rng = Range("C11:O14")
Fname = Range("C1").Value
strbody = "<BODY stylefont-size:14pt;font-family:Calibri>Hello All, please see the latest  Daily Data<p>\Teams with highest Sales.</BODY>"
Drng = Range("C2").Value
Email_Subject = "Daily Sales Report" & Drng
Email_Send_From = ""
Email_Send_To = ""
Email_Cc = ""
Email_Bcc = ""
Email_Body = strbody
Email_Attach = thisWb.Path & "\Daily Sales Report " & Fname & ".xlsx"

On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.htmlBody = strbody
.attachments.Add Email_Attach
.display
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

So any ideas on adding the range C11 to O14 as a picture as well as the text within 'strbody'.
Thanks
 
Sort by date Sort by votes
Dim strbody as String.

A string can't hold a picture.

But more fundamentally, you first need to convert the DISPLAY of a range of cells to some image format in order to have a picture to insert anywhere.
 
Upvote 0 Downvote
ALso, you do know that all the [blue]BLUE[/blue] variables are Variants, right?

Code: 
Dim Fname As String, strbody As String, Drng As String, Email_Attach As String, _[blue]
Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, [/blue]Email_Body As String, email_body2 As String
Dim [blue]Mail_Object[/blue], Mail_Single As Variant
Dim rng

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Thanks for the tips.
I've been able to convert the range into a picture
Code: 
Worksheets("Sheet1").Range("A4:H6").CopyPicture xlScreen, xlBitmap
Worksheets("Sheet1").Paste _
    Destination:=Worksheets("Sheet1").Range("A10")

However I still can't work out how to embed this into an email
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Question
Open Word as ReadOnly 1
Replies
4
Views
143
Andrzejek
Andrzejek
dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
501
SkipVought
S
K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
567
combo
combo
rf92d
  • Locked
  • Question
Hi referring to thread707-107529
Replies
0
Views
433
rf92d
rf92d
rf92d
  • Locked
  • Question
hi referring to thread707-103495
Replies
9
Views
535
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top