Thread
:
Error in macro - please help
View Single Post
#
1
Posted to microsoft.public.excel.programming
Ardus Petus
external usenet poster
Posts: 718
Error in macro - please help
Use Attachments (with an "s")
You also forgot to Quit Outlook:
HTH
--
AP
'--------------
Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String
For Each cell In ThisWorkbook.Sheets("E-mail Contacts") _
.Columns("i").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "test mail"
.Body = "See attachment"
.Attachments.Add ("c:\boot.ini")
.Send 'or use .Display
End With
OutApp.Quit
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
'------------------
"G and (ajk)" a écrit dans le message de
news:
...
Hi,
I am using the macro below to send an e-mail from excel (through outlook).
When there is no attachment to the e-mail (take that command out) it works
fine. However, when you try and attach a file it comes up it comes up
with
Run-time error 438. Object doesn't support this property or method.
Here is the macro - I would be grateful for any assistance.
Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String
For Each cell In ThisWorkbook.Sheets("E-mail Contacts") _
.Columns("i").Cells.SpecialCells(xlCellTypeConstan ts)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ""
.CC = ""
.BCC = strto
.Subject = ""
.Body = ""
.Attachment.Add ("")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Reply With Quote
Ardus Petus
View Public Profile
Find all posts by Ardus Petus