Email from excel problem 1

[Audissimo]

Hi!

I have a worksheet with e-mail addresses. Now, I am going to use those addresses like a mailing list. But, I have a problem with my code below;

Sub mailto_Selection()
Dim Email As String, Subj As String, cell As Range
Dim response As Variant
Dim msg As String, url As String
Email = "" 'create list below
Subj = "Family Newsletter"
msg = "Dear Family,"
'-- Create the URL

For Each cell In Selection
Email = Email & cell.Text & "; "
Next cell

url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
& Replace(msg, Chr(10), "/" & vbCrLf & "\")
MsgBox url
url = Left(url, 2025) 'was successful with 2025 , not with 2045
'-- Execute the URL (start the email client)
ShellExecute 0&, vbNullString, url, vbNullString, vbNullString, vbNormalFocus
End Sub

The problem is that row "ShellExecute...."causes error; "Sub or function not defined" and the code stopped here. It seems to be that code cannot open my Outlook application.

Anybody have an idea to solve this?

-JK

 

[RonRepp]

JK:

I've done this many times, but I always create an outlook object. These snippets are from a VB program, but I think you'll get the idea.

First: Loop through your cells.
======================================
Public Sub OpenXL()
Dim i As Integer
Set XL = New Excel.Application
XL.Visible = True
XL.Workbooks.Open ("emailleads2.xls")
XL.Range("A1").Activate
i = 1
Do

If ActiveCell.Text = "" Then Exit Sub
XL.ActiveCell.Offset(1, 0).Activate
FName = XL.ActiveCell.Value
LName = XL.ActiveCell.Offset(0, 1).Value
EMail = XL.ActiveCell.Offset(0, 8).Value
If FName = "" Then Exit Do
XL.ActiveCell.Offset(0, 9).Value = Date
Sendme EMail

NextEMail:
i = i + 1
Loop Until ActiveCell.Text = "" 'Or i = 50
QuitXL
End Sub
===========================================

2nd: Send the e-mail

Sub Sendme(ByVal SendTo As String)
Dim HTML As String
HTML = GetHTML 'This is another procedure that actually creates an HTML e-mail

Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItem(olMailItem)
myitem.HTMLBody = HTML'you can change this to myitem.Body
myitem.Subject = Subject
myitem.Recipients.Add (SendTo)
myitem.Send
End Sub
====================================

I hope this helps.

Ron

Ron Repp