Method or Data Member Not Found Error

[akins4lyfe]

i have a listbox control placed on a form, "frmEmail". Users can select email address and click on a button to send email to a selected contact. I keep getting the above error, on these lines
.To = strEmailRecipient
.Subject = StrSubject
.Body = StrBody
.Display

kindly assist.

thanks.

Codes

Private Sub Form_Load()
Me.lstMembers.ColumnHeads = True
Me.lstMembers.ColumnCount = 3
Me.lstMembers.RowSourceType = "Table/Query"
Me.txtMessagebdy = "There will be a meeting on this friday at 2PM in the boardroom."
Me.txtSubject = "Meeting Reminder"
Call Populate
End Sub

Private Sub Populate()
Dim strSQL As String

strSQL = "SELECT tblMember.[First Name], tblMember.[Last Name], tblMember.EmailAddress "
strSQL = strSQL & " FROM tblMember;"
Me.lstMembers.RowSource = strSQL
End Sub

Private Sub Command4_Click()
'check that at least one member have been selected
If lstMembers.ItemsSelected.Count = 0 Then
MsgBox "Please Select at least one member", vbOKOnly, "Required Field"
lstMembers.SetFocus
End If

'test for required fields
If IsNull(Me.txtSubject) Then
MsgBox "Please enter a subject", vbOKOnly, "Required Field"
Me.txtSubject.SetFocus
End If

If IsNull(Me.txtMessagebdy) Then
MsgBox "Please enter a message", vbOKOnly, "Required Field"
Me.txtMessagebdy.SetFocus
End If

'check for email addresses
Dim strEmailRecipient As String
Dim VarItem As Variant
Dim appOutlook As Outlook.Application
Dim StrSubject As String
Dim StrBody As String
Dim msg As DAO.Recordset
strEmailRecipient = Nz(lstMembers.Column(2, VarItem))
Debug.Print "Email address: & "; strEmailRecipient
If strEmailRecipient = "" Then
End If

'create new mail message and send to current contact
Set appOutlook = GetObject(, "Outlook.Application")
Set msg = appOutlook.CreatItem(olMailItem)
With msg
.To = strEmailRecipient
.Subject = StrSubject
.Body = StrBody
.Display
End With

End Sub

 

[PHV]

Set msg = appOutlook.Creat[!]e[/!]Item(olMailItem)


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

ALSO check the Auto Syntax Check in the VB Editor > Tools > Options -- Editor TAB.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

Also:

Dim msg As [b]DAO.Recordset[/b]
...
Set msg = appOutl.CreateItem(olMailItem)

???

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[PHV]

So, replace this:
Dim msg As DAO.Recordset
with this:
Dim msg As Outlook.MailItem

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[akins4lyfe]

i am new to VBA programming,..thanks for your assistance people, i eventually figured out those errors and corrected them, only to run into another one here: Run Time Error 429. ActiveX component can't create object. on line 13 (Set appOutlook = GetObject(, "Outlook.Application"))

any suggestions pls

'check for email addresses
Dim strEmailRecipient As String
Dim VarItem As Variant
Dim appOutlook As Outlook.Application
Dim StrSubject As String
Dim StrBody As String
Dim msg As Outlook.MailItem

strEmailRecipient = Nz(lstMembers.Column(2, VarItem))
Debug.Print "Email address: & "; strEmailRecipient
If strEmailRecipient = "" Then
End If

'create new mail message and send to current contact
Set appOutlook = GetObject(, "Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strEmailRecipient
.Subject = StrSubject
.Body = StrBody
.Display
End With

End Sub

 

[akins4lyfe]

im thinking, is this a registry issue? or a faulty Reference library,

pls assist.

thank you.

 

[PHV]

Use this instead:
Set appOutlook = CreateObject("Outlook.Application")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[akins4lyfe]

the error 429 still came back even though i have re-written my codes, here on the same line: (Set appOutlook = CreateObject("Outlook.Application")
)


For Each VarItem In lstMembers.ItemsSelected

'check for email addresses

strEmailRecipient = Nz(lstMembers.Column(2, VarItem))
Debug.Print "Email address: & "; strEmailRecipient
If strEmailRecipient = "" Then
GoTo NextContact
End If

'create new mail message and send to current contact
Set appOutlook = CreateObject("Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strEmailRecipient
.Subject = StrSubject
.Body = StrBody
.Display
End With

NextContact:
Next VarItem

errorhandlerexit:
Set appOutlook = Nothing
Exit Sub

errorhandler:

'outlook is not running, open outlook with create object
If Err.Number = 429 Then
Set appOutlook = CreateObject("Outlook.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number _
& "; Description: " & Err.Description
Resume errorhandlerexit
End If

 

[akins4lyfe]

I have now resolved this problem by re-installing the MS office suite, but the recipient field is now printed as follows:

"micweed1@gmail.com#mailto:micweed1@gmail.com#"

i would like to strip off the "mail to:" from contact email address, and only print email address into that field.

i have assigned strEmailRecipient = lstMembers.Colums(2). i got the same result.

any suggestions pls.

here's my new code:


strEmailRecipient = Nz(lstMembers.Column(2, VarItem))
Debug.Print "EmailAddress: & "; strEmailRecipient
If strEmailRecipient = "" Then
GoTo NextContact
End If

'create new mail message and send to current contact
Set appOutlook = GetObject(, "Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strEmailRecipient
.Subject = StrSubject
.Body = StrBody
.Display
End With

 

[combo]

Do you have reference to outlook library set? I'd start without error procedure and outlook closed. Create outlook instance once (now it's in loop). What happens after:

Set appOutlook = New Outlook.Application
For Each VarItem In lstMembers.ItemsSelected
'check for email addresses
strEmailRecipient = Nz(lstMembers.Column(2, VarItem))
Debug.Print "Email address: & "; strEmailRecipient
If strEmailRecipient = "" Then
GoTo NextContact
End If
'create new mail message and send to current contact
Set appOutlook = CreateObject("Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)

combo