Solved: Pick email address from cell in workbook

[Gil]

Hello
I am using the following code "Application.Dialogs(xlDialogSendMail).Show" to start an email with a macro.This works fine by attaching the workbook file and adding the filename as the subject in the email. What can I add to select an email address from a cell in that workbook and inserting it into the "TO" box in Microsoft outlook

:beerchug:

[mdmackillop]

Hi Gil
Welcome to VBAX
Can you post the whole of your code?

[Gil]

This is the macro I am using
Sub sendlog()
'
' sendlog Macro
' Macro recorded 23/06/2009 by 803090680
'
'
Application.Dialogs(xlDialogSendMail).Show

End Sub

[mdmackillop]

Sub sendlog()
Application.Dialogs(xlDialogSendMail).Show Range("A1")
End Sub

[Gil]

That worked perfectly. I did not think it was going to be that straight forward. If not too much trouble may I ask how to add a CC from another cell reference.

[mdmackillop]

Have a look at this page (http://www.codeforexcelandoutlook.com/excel-vba/send-email-from-excel/)

[Gil]

Thank you for the reply & redirection. Not quite as straightforward as the original answer. Being a novice I will attempt but so far cant work it out.:help

[mdmackillop]

Option Explicit

Sub eMailActiveDocument()
'Requires reference to Microsoft Outlook xx Object Library
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveWorkbook
Doc.Save
With EmailItem
.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "User@Domain.Com" 'or cell reference
.cc = "Someone@vbax.com" 'or cell reference
.Importance = olImportanceNormal
.Attachments.Add Doc.FullName
.display 'Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub

[Gil]

Hello

Many thanks for your help and guisdance. From what you last sent me I have ended up with the following that seems to work ok. The 'subject' I am using a cell ref that has the filename added to it by formula.I took out these bits as they just appeared in the 'body' when the email displayed.

.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"

And this is what works for me


Option Explicit

Sub eMailActiveDocument()
'Requires reference to Microsoft Outlook xx Object Library
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveWorkbook
Doc.Save
With EmailItem
.Subject = Range("c28")
.Body = "" & vbCrLf
.To = Range("f4") 'or email address
.cc = Range("f6") 'or email address
.Importance = olImportanceNormal
.Attachments.Add Doc.FullName
.display 'Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub

Best regards

Gil (the novice)