VBA question

[selenay]

This may be a long-shot, but...

Does anyone know how to use VBA to send an email with an embedded Excel chart? I've been wracking my brains, Googling obsessively and glaring at code, but to no avail. Recording the action as a Macro in Excel isn't helping because it doesn't show me doing the bit where I use File/Send To and definitely won't see anything I do in Outlook.

*sigh*

Got such a headache.

The good news is that the boss approved the VBA books I found so I'll be ordering them tonight and putting in an expenses claim. Woo!

Hopefully I'll at least find a hint about it in one of them. I feel rather less like Queen of Code than I usually do right now.

Guess I really need that pub trip with The Girls tonight.

Comments

[paranoidangel]

I don't know if this will help, but our Timesheet has a button that you press to email it to an address. It calls a sub to check it first, so you can ignore that bit, but I thought I should paste it in exactly in case I delete something useful (I didn't write this).

Sub EmailTimesheet()
Dim appOutlook As Outlook.Application 'outlook application object
Dim otlkMessage As Outlook.MailItem 'message mail item
Dim otlkRecipient As Outlook.Recipient 'recipient for the mail message
Dim otlkAttachment As Outlook.Attachments 'attachment for the e-mail
On Error GoTo EmailTrap
'code to send the email to timesheets
'calling the check macro
TimesheetCheck
'if the timesheet is a ok, it is e-mailed
If boolTimeSheetAOk = True Then
'creating the outlook application
Set appOutlook = New Outlook.Application
'creating the outlook message
Set otlkMessage = appOutlook.CreateItem(olMailItem)
'creating the message subject
otlkMessage.Subject = "Timesheet " & Range("C3").Value & " W/C " & Range("C2").Value
'setting the recipient
Set otlkRecipient = otlkMessage.Recipients.Add("Timesheets")
'setting the recipient type
otlkRecipient.Type = olTo
'saving the current workbook
ThisWorkbook.Save
'creating the attachment object
Set otlkAttachment = otlkMessage.Attachments
'setting the attachment object to be the current workbook
otlkAttachment.Add ThisWorkbook.FullName
'displaying the current workbook
otlkMessage.Display
End If
EmailTrap:
If Err.Number <> 0 Then
MsgBox "An error has occured while attempting to Email the timesheet." & vbCrLf & vbCrLf & _
"Please contact Nic Mayer with the following information : " & vbCrLf & _
"Error Number = " & Err.Number & vbCrLf & "Error Description = " & _
Err.Description, vbCritical, "Email Timesheet Error"
End If
End Sub

[selenay]

Thanks for the code. It utlised a slightly different method of attaching workbooks that I may try, but the boss' boss has asked me to embed the chart into the email body so I'll have to keep hunting :-( I can embed just about anything else from a spreadsheet into the body, but haven't the faintest idea with charts!

[paranoidangel]

Doesn't want much, does he?

[gmul.livejournal.com]

I think I have a slightly different way of doing it to the above but all the details are probably on a machine at the office, where I won't be until after Easter at the earliest. I'll check later though and see if I've not got a backup with me or something. Might possibly need some version of .Net, for what it's worth.

I was so happy when we eventually found a ready rolled alternative for what I'd been trying to write!

[selenay]

I've been encouraging Da Boss to look at .Net (or let me look...) and see whether it can improve our automation processes - one of the books that he's authorised me to buy for the department includes a chapter or two on .Net that might be enough to get me started :-)

Until that however...

We're trying to send an email with the chart embedded in the body. I can embed cell ranges in an HTML body and sort out plain text with no trouble, but this chart thing is defeating me *sigh* Hopefully the books that I've orderd will arrive some time next week so I can see whether there are any tips to get me started on this in those. So far, the only thing I can find when Googling is...a reference to one of the books that I've ordered :-)

[gmul.livejournal.com]

I take it you've had a cautious prod at http://www.mrexcel.com/archive2/54600/63379.htm
?