build list for eMail

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I am using this:
Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath, Optional
PrimRec, Optional SeconRec, Optional BlindRec, Optional SubjectString,
Optional BodyString)
which is from KB: http://support.microsoft.com/kb/161088/en-us
(with a little tweaking)
I am trying to build an eMail list for "To:" (PrimRec) using the following
code:
Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim qdf As DAO.QueryDef
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

Set DB = CurrentDb
Set qdf = DB.QueryDefs("jUtilitiesContacts")
qdf.Parameters(0) = [Forms]![fCuts]![JobID]
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)

With rs
Do While Not .EOF
strOut = strOut & ![ContactEmail] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

Debug.Print BulkEmail

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
DB.Close: Set DB = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function

I am getting err 3265 Item not found in this collection. I've checked all
my spelling. Now it is driving me mad. Please help me with this, or is
there a better method for handling this.
Thanks,
Nick
 
By the way. When I run it all together (code generates PDF from Report,
Saves to My Documents, then is supposed to place in eMail) I get this error
" there must be one name in the To, CC..."
I am trying to build an eMail list for "To:" (PrimRec) ...
Set objOutlookRecip = .Recipients.Add(PrimRec)
Just FYI. I understand why this is, because my other function is crapping
out.

Nick X said:
Hi all,
I am using this:
Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath, Optional
PrimRec, Optional SeconRec, Optional BlindRec, Optional SubjectString,
Optional BodyString)
which is from KB: http://support.microsoft.com/kb/161088/en-us
(with a little tweaking)
I am trying to build an eMail list for "To:" (PrimRec) using the following
code:
Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim qdf As DAO.QueryDef
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

Set DB = CurrentDb
Set qdf = DB.QueryDefs("jUtilitiesContacts")
qdf.Parameters(0) = [Forms]![fCuts]![JobID]
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)

With rs
Do While Not .EOF
strOut = strOut & ![ContactEmail] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

Debug.Print BulkEmail

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
DB.Close: Set DB = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function

I am getting err 3265 Item not found in this collection. I've checked all
my spelling. Now it is driving me mad. Please help me with this, or is
there a better method for handling this.
Thanks,
Nick
 
Back
Top