Problem auto generating e-mail to several recipients - argument not optional error message

[nilshalvarsson]

Hello,

In a work sheet, I have a list of e-mail addresses. I am trying to
write the code for a button that sends an e-mail, with all the
addresses in the list as recipients. What the code basically does is
that a for statement runs through the list and adds the recipients via
the Set objRecipient command. I keep getting the "argument not
optional" error message, pointing to the row with the Set objRecipient
command. The only code I've written myself is the for statement, the
rest is ripped off this forum. Could anyone of you VBA gurus have a
look through the code and give me some suggestions as to how to fix
it. The for statement itself is probably not excellently written, so
if you have any input on how to improve it or make it neater, please
share.

Is it possible to send a hyperlink to the workbook (or even better to
a specific worksheet in the workbbok) in the body of the e-mail? The
application will be posted on a company intranet, so the link should
work.

Thanks alot
Nils

Private Sub CommandButton2_Click()
Dim objOutlook As Object
Dim objMailItem As Object
Dim objRecipient As Object
Dim objNameSpace As Object
Dim i As Integer
Dim cell As Range


Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
objNameSpace.Logon , , True


Set objMailItem = objOutlook.CreateItem(0)

For i = 8 To 19 ' The e-mail
list is in the range D8-D19

Set cell = Range("D" & i)
cell.Select

If IsEmpty(ActiveCell) Then ' Checks if the
cell is empty
GoTo nextLine
End If

Set objRecipient = objMailItem.Recipients.Add.cell.Value '
This is where I get "Argument not optional"
objRecipient.Type = 1

nextLine:

Next i

objMailItem.Subject = "Subject"
objMailItem.Body = "Body" ' Is it possible to
attach a link to this workbook here?
objMailItem.Send

End Sub

 

[vbapro]

There is an erratum in your code. Replace the line

Set objRecipient = objMailItem.Recipients.Add.cell.Value

with this one

Set objRecipient = objMailItem.Recipients.Add(cell.Value)

 

[nilshalvarsson]

vbapro,

Yes, that did the trick. Thank you!

Any ideas how I can attach the link to this workbook in the body text?
Is there a command that captures the address to the active excel
workbook?

Thanks for your help

//Nils

 

[vbapro]

What address (or kind of link) of the workbook you mean? Its full path on
your computer can be obtained from ActiveWorkbook.FullName. If you would like
the file to be accessed in a network or via Internet then you should add the
network address to the file name.

objMailItem.Body = ActiveWorkbook.FullName

Another way is to send the whole file, for doing this just add these lines
before sending:

ActiveWorkbook.Save
objMailItem.Attachments.Add ActiveWorkbook.FullName

 

[vbapro]

What address (or kind of link) of the workbook you mean? Its full path on
your computer can be obtained from ActiveWorkbook.FullName. If you would like
the file to be accessed in a network or via Internet then you should add the
network address to the file name.

objMailItem.Body = ActiveWorkbook.FullName

Another way is to send the whole file, for doing this just add these lines
before sending:

ActiveWorkbook.Save
objMailItem.Attachments.Add ActiveWorkbook.FullName

 

[nilshalvarsson]

vbapro,

Thanks for your time and speedy responses!

The file will be published on a private intranet and I think that the
ActiveWorkbook.FullName will suffice. I've tried it out and it works
fine. When the file is published on the intranet,
ActiveWorkbook.FullName captures the full network address.

Is it somehow possible to make the path into a hyperlink, just showing
the file name, and attach it to the e-mail?. I have managed to paste
the file name as a hyperlink in a worksheet by using the code below,
but I can't manage to get the link into the e-mail body text. Any
ideas?

Dim FName As Variant
Dim sStr As String
Dim iPos As Long

FName = ActiveWorkbook.FullName

iPos = InStrRev(FName, Application.PathSeparator)
sStr = Mid(FName, iPos + 1)

ActiveCell.Hyperlinks.Add _
Anchor:=ActiveCell, _
address:=FName, _
TextToDisplay:=sStr

//Nils

 

[vbapro]

Instead of objMailItem.Body use the following two lines

objMailItem.BodyFormat = olFormatHTML
objMailItem.HTMLBody = "<HTML><BODY><A HREF=""file://" &
ThisWorkbook.FullName & """>Catch the file !</A></BODY></HTML>"

 

[nilshalvarsson]

I get an error message: "Invalid procedure call or argument" on the
line "objMailItem.BodyFormat = olFormatHTML "

 

[nilshalvarsson]

If I skip objMailItem.BodyFormat = olFormatHTML, the code executes
perfectly!

Thank you vbapro!

//Nils