VB Macro - Email Reference

[MartinEbner]

I have modified a macro (please see below) - it encompasses all of the functionality that I require with one exception. I require it to reference an email address that is written into a cell.

Macro
Sub eMailActiveDocument()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document

Application.ScreenUpdating = True
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
With EmailItem
.Subject = "Subject"
.Body = "Dear Whoever,
.To = "User.Domain.Com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.Fullname
.Display
End With

Application.ScreenUpdating = True

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

End Sub


Any help would be greatly appreciated.

Regards,
Martin

 

[Michael M]

try

.To = Range("A1").value ' change range to suit

 

[Biz]

Try code below

Sub eMailActiveDocument()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Dim rng As Range

set rng =Range("A2")  ' Change cell address as required - email address

Application.ScreenUpdating = True
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
With EmailItem
.Subject = "Subject"
.Body = "Dear Whoever,"
.To = rng
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.FullName
.Display
End With


Application.ScreenUpdating = True


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


End Sub

 

[MartinEbner]

Wonderful thank you. However, what if this code was embedded in a word document. How would I reference to an excel sheet?

 

[Macropod]

I see you have posted almost exactly the same question here: http://www.mrexcel.com/forum/genera...374-visual-basic-applications-macro-word.html

The only difference I can see is that, in this thread, you mention Excel - but even then the code you've posted is for Word.

Perhaps you could clarify your requirements...

 

[Macropod]

I see you've also posted almost exactly the same question at:
http://www.vbaexpress.com/forum/showthread.php?54774-VBA-Macro-Word

 

[Michael M]

Hmmm....I'll step away from this one.
I can see a lot of people trying to solve this at once !!