PDA

View Full Version : Sleeper: Inserting email address



johnske
05-24-2006, 02:40 AM
I'm looking at an alternative to the usual methods for insert the active sheet into the body of an email. You can use tools native to excel to do this with a couple of clicks, so I'm using the 'Execute' method to run those tools inside a procedure.

The only thing holding me up at present is how to put the email addy in the "To" box. If I run it once and manually insert an email addy, that addy is memorized, but I want to be able to change/insert the addy with code. Any suggestions?

Here's what I have so far: (probably best to comment out Application.ScreenUpdating = False to see what's happening at the moment)


Sub TryThisOne()
Application.ScreenUpdating = False
With Application.CommandBars("Send To")
'//IF the control "Send Now" is missing the code will error out, so add it\\
.Controls.Add Type:=msoControlButton, ID:=3708, Before:=1
.Controls("Mail Recipient").Execute
DoEvents
With .Controls("Send Now")
.Execute
'//remove the "Send Now" control that was added for this procedure\\
.Delete
End With
End With
Application.ScreenUpdating = True
End Sub

Killian
05-24-2006, 03:42 AM
Hi John,

you can get to the resulting mailitem object with ActiveSheet.MailEnvelope.ItemNeedless to say, doing so will trigger the Outlook Object Model Guard :whyme:

johnske
05-26-2006, 12:10 AM
Hi John,

you can get to the resulting mailitem object with ActiveSheet.MailEnvelope.ItemNeedless to say, doing so will trigger the Outlook Object Model Guard :whyme:Hi Killian,

I'm having no luck at all with this (Office 2k).

Unfortunately I don't know exactly what Excel is using for it's default mailer, but I would assume it's linked in some way with Outlook (about which I know zilch), but I know this sends mail via my own default mailer (Outlook Express). Surely it can't be that hard to programmatically insert a recipients addy in the "To" field of Excels default mailer?

Regards,
John

PS: I tried recording this, but the actions aren't showing in the recorded macro

mdmackillop
05-26-2006, 12:55 AM
Hi John,
Here's my complete code for emailing with attachments. Maybe you can pick some bits out of it.
Regards
Malcolm



Option Explicit
Sub eMailValuation()
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim MySub As String
Dim MyText As String
Dim MyVal As String
Dim MySum As String
Dim MyAve As String
Dim MyRecipient As Object
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
'Create subject
MySub = "Kitchen Project - Valuation No. " & Range("ValNo")
'Create text
MyText = "Please find attached our Valuation No. " & Range("ValNo") & _
" together with a list of included properties and summary of average costs." & vbCr & vbCr & _
"Regards," & vbCr & vbCr & "Malcolm"
'Attachments
MyVal = "F:\Surveyor\Valuations\Val" & Format(Range("ValNo"), "000") & ".mdi"
MySum = "F:\Surveyor\Valuations\Summary.mdi"
MyAve = "F:\Surveyor\Valuations\Averages.mdi"
'Create email
With EmailItem
.Subject = MySub
.Body = MyText
Set MyRecipient = .Recipients.Add("Valuations")
MyRecipient.Resolve
.Importance = olImportanceNormal
.Attachments.Add MyVal
.Attachments.Add MySum
.Attachments.Add MyAve
.Save
.Display
End With
Application.ScreenUpdating = True
Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing
Set MyRecipient = Nothing
End Sub

johnske
05-26-2006, 02:40 AM
Thanx Malcolm,

But I've already tried to use Outlook to set the addy with no luck. I've also tried to use "Route" and "SendMail" to try and do the same - no luck there either... (it's sent as an attachment)

I've been using my own variation of Ron De Bruins code (http://www.rondebruin.nl/mail/folder3/mail2.htm) to email the active sheet in the body of the email (this is also a fairly frequent question on boards). The problems here are:

1) The method converts the worksheet values to text, it can't handle shapes/images.

2) It uses Outlook and I've found that (as I - and lotsa others - don't use Outlook as a default emailer) it's not sent until Outlook is opened - something I never do... So in other words it's not sent.

On the other hand, the inbuilt Excel emailer sends the complete sheet in the body (including shapes/images) and automatically uses whatever the user has as their default mailer to send it.

In the past I've suggested to posters that they put the "Send To" and "Send Now" controls on their command bars but this is usually considered unsatisfactory - they want it completely automated and this is why I've started on this approach i.e. to use VBA code to click those 2 buttons. But to make this a satisfactory option I also need code to fill out the "To" field. As I said, surely this is not impossible...

Regards,
John :)

Killian
05-26-2006, 05:09 AM
It seems to me that the core of the issue is that sending directly from Excel requires it to identify the mail app through it's MailSystem object. This has 3 possible returns:
xlMAPI - MS Outlook of some type
xlPowerTalk - Mac
xlNoMailSystem
Consequently, under Windows, if a MAPI object can't be created it just won't send anything.

A further frustration is that even if you do have Outlook, the SendMail method (where you can specify recipients) only applies to the Workbook (as an attachment or link). The method for sending the worksheet in the body isn't exposed in the OM.

I can't find a way around this in VBA. You would need to write a library with methods to determine the Mail app and provide an interface to create a mail item and specify it's properties. Then come up with a way of embedding the worksheet... :think:

johnske
05-27-2006, 06:31 AM
Well I suppose the nearest we can get to fully automating this is something similar to what I've got so far (part shown below). After all, what's the real difference between writing the email addies into the code when it's pasted in, or entering them into the field the very first time the code's used in the workbook?

I worked around the problem by using a userform to give the user further choices such as specifying the same recipients, different recipients or... to never show the userform again after the 1st time.

I suspect my original question'll remain an open question for a while, so I'll submit what I have to the KB so it can be tested on machines and default emailers other than my own (OE).

From what I've been able to gleen from the net so far, it should work on everything from Office 2000 up and for all default email progs. (Note, it also sends chart sheets, charts, shapes, images, etc., etc. in the body :))


Option Explicit

Sub SendActiveSheetInBodyOfEmail()
'NOTE: If you've clicked the "never show" button and later wish
'to display the options userform again, go to File > Properties >
'Summary and delete the NeverShow displayed in that field...
If ActiveWorkbook.BuiltinDocumentProperties("Comments") = "NeverShow" Then
Call AutoSend
Else
ChoiceForm.Show False
DoEvents
End If
End Sub


Private Sub AutoSend()
With Application
.ScreenUpdating = False
With .CommandBars("Send To")
'//IF the control "Send Now" is missing the code will error out, so add it\\
.Controls.Add Type:=msoControlButton, ID:=3708
.Controls("Mail Recipient").Execute
DoEvents
With .Controls("Send Now")
'sends activesheet in email body
.Execute
'//remove the "Send Now" control that was added for this procedure\\
.Delete
End With
End With
.ScreenUpdating = True
End With
End Sub


Private Sub ManualSend()
Application.CommandBars("Send To").Controls("Mail Recipient").Execute
End Sub