Object Variable or With Variable Not Set

You have it remmed out:

'Dim MyNewBodyText As TextStream      ' ***
Dim MyBodyText As String

But you have this later:

MyNewBodyText = MyBodyText
     ^^^^

mx

Hi MX,

Thanks for the reply.  I did uncomment that and it stops and gives me the same error at

MyNewBodyText = MyBodyText

Cody-

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

should be:

MyNewBodyText = Replace(MyNewBodyText, "FirstName", MailList("FirstName"))

??  ie.  Why double brackes?

The double brackets it is the way it is input into the txt file for the body.

Well you have

Dim MyNewBodyText As TextStream
Dim MyBodyText As String

and

MyBodyText = MyBody.ReadAll  ' Stream .. ?
'and close the file.
MyBody.Close

MyNewBodyText = MyBodyText   ' MyBodyText   is Text ??

mx
 

This:

MyBodyText = MyBody.ReadAll
'and close the file.
    'MyBody.Close              '  Try moving the Close below the next line

MyNewBodyText = MyBodyText

MyBody.Close    '  *********************

Moved it, still stops at  MyNewBodyText = MyBodyText

Try this and comment:

Public Function EMailTest()
  
    Dim db As DAO.Database
    Dim MailList As DAO.Recordset
    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    Dim Subjectline As String
    Dim BodyFile As String
    Dim fso 'As FileSystemObject
    Dim MyBody 'As TextStream
    Dim MyNewBodyText 'As TextStream
    Dim MyBodyText As String
  
    Set fso = CreateObject("Scripting.FileSystemObject")

 
    Subjectline$ = "Email Test"
    BodyFile$ = "C:\TestEmail.txt"
 
    'Checks to see if body file is there
 
    If fso.FileExists(BodyFile$) = False Then
        MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "I Ain??t Got No-Body!"
        Exit Function
    End If
 
    'Since we got a file, we can open it up.
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
    'and read it into a variable.
    MyBodyText = MyBody.ReadAll
    'and close the file.
    MyBody.Close
 
    MyNewBodyText = MyBodyText
  
    ' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
    MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
    MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))
 
    'Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application
  
    'Set up the database and query connections
    Set db = CurrentDb()
  
    Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")
 
    'now, this is the meat and potatoes.
    'this is where we loop through our list of addresses,
    'adding them to e-mails and sending them.
 
    Do Until MailList.EOF
 
        'This creates the e-mail
        Set MyMail = MyOutlook.CreateItem(olMailItem)
  
        'This addresses it
        MyMail.To = MailList("Email")
  
        'This gives it a subject
        MyMail.Subject = Subjectline$
  
        'This gives it the body
        MyMail.Body = MyNewBodyText
  
        'This sends it!
        MyMail.Send
 
        'And on to the next one...
        MailList.MoveNext
    Loop
  
    'Cleanup after ourselves
    Set MyMail = Nothing
    Set MyOutlook = Nothing
  
    MailList.Close
    Set MailList = Nothing
    db.Close
    Set db = Nothing
  
End Function

Select allOpen in new window

Could I change this to a DoCmd.SendObject?  Seems like it would be easier.  I pulled this code off of this website:

http://www.jephens.com/2007/05/13/how-to-send-e-mail-from-ms-access-using-outlook/

What is the exact error msg now ?

mx

hnasr,

still stops at:

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

Still the same error

mx,

It is the same error

Object Variable or With block Variable Not Set

How about dimming both to Stream

Dim MyNewBodyText As TextStream
Dim MyBodyText As TextStream
 

MX,

Same error with dimming both to TextStream

What is the exact error now and what line does it occur on?

Also, have you tried Debug>>Compile ... to be sure there are no compile errors?

mx

Did Debug>Compile.  After dimming to textstream it stops at:

MyBodyText = MyBody.ReadAll

Same error

Well, undim that as Stream I guess.

What is the Error message ?

mx

codyvance1,
When I run the code from the immediate window, it displays the message "The body file isn't..."

After all these comments guess you need to attach a sample of the database.

Hnasr,

If you create a file C:\TestEmail.txt with the following in the body:

[[Firstname]]: Test Email # of Guests: [[GuestCount]]

It will not give that to you.

MX,
It now stops at:

MyNewBodyText = MyBodyText

With same error.

"With same error."

What is the *error message* ?  Is it still "Object Variable or With Variable Not Set"

?

Yes still that error MX

Post the exact latest code you have now ... with changes.  Please post in this text box, not the 'Code' window.

mx

Public Function EMailTest()
 
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyNewBodyText As TextStream
Dim MyBodyText As String
 
Set fso = New FileSystemObject

Subjectline$ = "Email Test"
BodyFile$ = "C:\TestEmail.txt"

'Checks to see if body file is there

If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain??t Got No-Body!"
Exit Function
End If

'Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
'and read it into a variable.
MyBodyText = MyBody.ReadAll
MyNewBodyText = MyBodyText
'and close the file.
MyBody.Close
 
' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))

'Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
 
'Set up the database and query connections
Set db = CurrentDb()
 
Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

'now, this is the meat and potatoes.
'this is where we loop through our list of addresses,
'adding them to e-mails and sending them.

Do Until MailList.EOF

'This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
 
'This addresses it
MyMail.To = MailList("Email")
 
'This gives it a subject
MyMail.Subject = Subjectline$
 
'This gives it the body
MyMail.Body = MyNewBodyText
 
'This sends it!
MyMail.Send

'And on to the next one...
MailList.MoveNext
Loop
 
'Cleanup after ourselves
Set MyMail = Nothing
Set MyOutlook = Nothing
 
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
 
End Function

Try changing

MyNewBodyText = MyBodyText

to

Set MyNewBodyText = MyBodyText

mx

Now i get type mismatch on that line.

Add this line to declaring variables
Const ForReading = 1, ForWriting = 2, ForAppending = 3

What do you want to replace in this line?
    MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

Send or list a sample of the text file.

Problem is I'm not familiar with the Mail object model.

How about instead of

MyBodyText = MyBody.ReadAll
MyNewBodyText = MyBodyText

this:

Set MyNewBodyText = MyBody.ReadAll
'  MyNewBodyText = MyBodyText

Ok now I get input past end of file.  Give me a second and I will send a sample DB.

Soooo ?  Why do I need a sample DB ?

mx

Here is the DB.
Sample.mdb

Just figured it would be easier for you to look at then going back and forth.  Here is the txt file as well.
TestEmail.txt

ok ... so where and what is the error now?

mx

Right now i get type mismatch at

Set MyNewBodyText = MyBody.ReadAll

OK .... first ... change

Dim MyNewBodyText As TextStream

To

Dim MyNewBodyText As Variant.

This will get us back to the original error line (same error):

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
          ' This has not been Set anywhere                                    ^^^^^^^

You Dimmed at the top      

Dim MailList As DAO.Recordset
 
but that's it.  

Somewhere   ... ok ... this below

Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

needs to be before      

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

like this I guess:

Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

?

mx

Try this and comment:

Sample-2.mdb

Yes hnasr that does work but does not replace the variables in the txt file with the values in the recordset.

And yet one more issue ... db  not set. This works:

Dim MyNewBodyText As Variant

' more ....

MyNewBodyText = MyBodyText
Set db = CurrentDb
Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")
' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))

Ok MX, you totally lost me... Can you reupload the DB for me to see please.

Standby ... still an issue ... Replace not replacing FirstName, only GuestCount ..

mx

Thank you MX, I really really appreciate it, this has been driving me up the wall!!

THANK YOU!!!!  You are a savior MX.

And ... make this change:

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"), , , vbDatabaseCompare)
^^^^^^^^^^^^^^^^^^^^^
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"), , , vbDatabaseCompare)

The last issue was Replace was not finding

[[FirstName]]

Because it's actually:

[[Firstname]]
         ^
So ... that option eliminates the Case issue.

mx

Well ...  the    can be avoided by have the use Option Compare Database in the Declarations section of the module

Option Compare Database  ' ******** This was missing - causing Replace to be Case sensitive
Option Explicit

Ahhhh I did not know this...  I had issues before that changing from Compare to Explicit fixed so I just thought that might be the same issue.

MX one more thing, do you know where I could input to set the ActionTaken filed to true after it sends?

Thank You

Sorry ... I had to leave work ... missed your other Q.

mx