View Full Version : Solved: Pick email address from cell in workbook
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
07-12-2009, 03:05 AM
Hi Gil
Welcome to VBAX
Can you post the whole of your code?
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
07-12-2009, 03:34 AM
Sub sendlog()
Application.Dialogs(xlDialogSendMail).Show Range("A1")
End Sub
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
07-12-2009, 09:39 AM
Have a look at this page (http://www.codeforexcelandoutlook.com/excel-vba/send-email-from-excel/)
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
07-12-2009, 01:50 PM
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
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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.