Sending e-mail through VBA

[Dan190984]

Hi all

I am currently running a macro with the following script.....

Sub Understood()
Dim OutlookApp As Object
Dim Mess As Object, Recip As String, rng As Range
Set rng = Range("B3:K28")
Recip = "name@companyname.com"
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = "Subject"
.HTMLBody = RangetoHTML(rng)
.Recipients.Add Recip
.cc = ['[sheetname.xlsm]workbookpage'!$g$9]
.Send
End With
End Sub

This works fine until the following issue occurs.....

When the page with this macro is sent to someone it is sent as a copy of a workbook so the CC section of the script fails as the filename is wrong.

How to I change this script to read that it needs to find G9 in the active workbook.

Thanks in advance

 

[VoG]

Try

.cc = ActiveWorkbook.Sheets("workbookpage").Range("G9").Value

 

[Dan190984]

Hi

I get this error with that attempt

TypeMismatch: Unable to coerce parameter value. Outlook cannot translate your string

Any ideas?

 

[VoG]

I didn't get an error but I didn'[t receive the .cc either

Make sure there is a valid e-mail address in that cell.

 

[Dan190984]

I have other macros running on the same S/S which will send to the address in G9.

Infuriating

 

[VoG]

I didn't get an error but I didn'[t receive the .cc either

I had the .cc addy in the wrong cell

Working perfectly here.

 

[Dan190984]

Working here now to - thanks!

 

[Dan190984]

Any idea how to help me on this issue?

http://www.mrexcel.com/forum/showthread.php?t=563107