By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

A97 > 2000 problem

P: n/a
The code posted below creates an email in outlook. It works fine in A97.
However when one of my users moved up to 2000 then an error message - 91 object
variable or with block variable not set - and the routine appeared stuck in a
loop, needed ctr alt del to stop. Any thoughts on what is wrong ?
David B
Private Sub Command68_Click()
On Error GoTo Handler

If (Me!Textcount) = 0 Then
MsgBox "There are no records to send"
Exit Sub
Else
DoCmd.OpenQuery "bcmsdeletebirthtable", acNormal, acEdit
DoCmd.Close acQuery, "bcmsdeletebirthtable"
DoCmd.OpenQuery "bcmsbirths", acNormal, acEdit
DoCmd.Close acQuery, "bcmsbirths"
End If
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strMessage As String
Dim strsubject As String
Dim strspareline As String
Set rs = db.OpenRecordset("BCMSREGgrab")

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Dim olObj As Outlook.Application

Dim olMail As Outlook.MailItem

Set olObj = New Outlook.Application

Set olMail = olObj.CreateItem(olMailItem)
Set olMail = olObj.CreateItem(olMailItem)

Do
strMessage = strMessage & vbCrLf & Trim(rs![Tag No] & Chr(124) & rs![DateOB] &
Chr(124) & rs![Sex] & Chr(124) & rs![Breeds] & Chr(124) & rs![electID] &
Chr(124) & rs![Dam I D] & Chr(124) & rs![surrdamid] & Chr(124) & rs![Ear Tag] &
Chr(124) & rs![Holding No] & Chr(124) & rs![birthherdsuffix] & Chr(124) &
rs![Holding No] & Chr(124) & rs![postherdsuffix]) 'data
strsubject = Trim(rs![BCMSapplicID] & Chr(124) & rs![BCMSVno] & Chr(124) &
rs![BCMSorigionater ID] & Chr(124) & rs.RecordCount & Chr(124) & rs![timestamp])
'header
strspareline = ""
rs.MoveNext

Loop Until rs.EOF

With olMail

.Subject = ""
.Body = strsubject & vbNewLine & strspareline & vbNewLine &
strMessage & vbNewLine
.To = "da**@sis.defra.gsi.gov.uk"
.Send
End With
Set olMail = Nothing
End If

exitsub:

olObj.Quit
Set olObj = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Email has been created. Open Outlook then press F5 to dial"

Dim stAppName As String

stAppName = "C:\Program Files\Microsoft Office\Office\outlook.exe"
Call Shell(stAppName, 1)
pbooClickTest = True
Exit Sub

Handler:
Select Case Err.Number
Case Else
MsgBox Err.Number & " " & Err.Description
Resume exitsub
End Select
End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Thanks for the reply. Checked references and were as they should be.
DB

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in message
news:dn********************************@4ax.com...

Check the references. The reference for OutLook in that database is
probably still set for Outlook 97 (or 98), rather then Outlook 2000
(version 9).

On Wed, 15 Oct 2003 14:38:18 +0100, "David B"
<da***@marleycotenospam.fsnet.co.uk> wrote:
The code posted below creates an email in outlook. It works fine in A97.
However when one of my users moved up to 2000 then an error message - 91 objectvariable or with block variable not set - and the routine appeared stuck in aloop, needed ctr alt del to stop. Any thoughts on what is wrong ?

--
When There's A Will, I Want To Be In It.


Nov 12 '05 #2

P: n/a

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in message
news:va********************************@4ax.com...
<Snip> The other thing I would look at is how the conversion was done. Did
they just allow Access to do it, or were the "recommended" steps
followed (Decompile, compact, convert)? If you're unsure, you may
want to use Lyle's BackThisUpText routine to accomplish much of the
same thing in code. (If you don't have Lyle's routine, search for it
at Google. Then keep it in your library of useful routines!)
Any more clues about Lyle`s routine. Havn`t been able to track it down.
TIA
David B

On Wed, 15 Oct 2003 21:24:24 +0100, "David B"
<da***@marleycotenospam.fsnet.co.uk> wrote:
Thanks for the reply. Checked references and were as they should be.
DB

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in message
news:dn********************************@4ax.com.. .

Check the references. The reference for OutLook in that database is
probably still set for Outlook 97 (or 98), rather then Outlook 2000
(version 9).

On Wed, 15 Oct 2003 14:38:18 +0100, "David B"
<da***@marleycotenospam.fsnet.co.uk> wrote:

>The code posted below creates an email in outlook. It works fine in A97.
>However when one of my users moved up to 2000 then an error message - 91

object
>variable or with block variable not set - and the routine appeared
stuckin a
>loop, needed ctr alt del to stop. Any thoughts on what is wrong ?
--
When There's A Will, I Want To Be In It.

--
Okay, Who Put A Stop Payment On My Reality Check?


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.