Trouble with automated email

[wtrimble]

I have code that would scan a recordset and email a user the records that met a certain condition.

It worked fine until I added a lot more records to see how long it would take to scan. However after I did that, whether it had any effect on it or not, I now get an error message that states :

"Application-defined or object-defined error" when it gets to ".send"

I've added the Microsoft Outlook object library to my references so I'm not sure why it is doing this. Also When I type "mailoutlook." the "send" function is available, so I am confused.

Can anyone see anything wrong in my programming that might be creating this error? Thanks for any help

Dim myrecordset As Recordset
Dim database As database
Dim olapp As Outlook.Application
Dim mailoutlook As Outlook.MailItem
Dim msg As String
Set olapp = CreateObject("outlook.application")
Set mailoutlook = olapp.CreateItem(olMailItem)
Set database = CurrentDb
Set myrecordset = Me.Recordset
msg = ""
myrecordset.MoveFirst
Do While Not myrecordset.EOF
    If Me.datedue - Date = 3 Then
        msg = msg & "Sample number: " & myrecordset![sample] & vbCr & vbCr
    End If
 
myrecordset.MoveNext
Loop
With mailoutlook
    .Subject = "Late Samples"
    .Body = "The following Samples are due in 3 days: " & vbCr & msg
    .To = "user email address"
    .Send
End With
Set olapp = Nothing
Set mailoutlook = Nothing
DoCmd.Quit
 
end sub

 

[wtrimble]

Anyone see anything wrong?

 

[boblarson]

Do you actually have this line exactly as shown?

.To = "user email address"

or do you have a different one. It would seem that it can't send if this is the way it shows.

 

[wtrimble]

No, it's a real email address

 

[boblarson]

No, it's a real email address

Well, other than that I don't see anything that looks like it would cause a problem.

(oh, one tip - you don't need to use .MoveFirst in your recordset when opening it. It already is at the first when opened. You might need to do that if you had to do a .MoveLast and then a .MoveFirst if you wanted the record count)

 

[darbid]

What happens when you take out the .send and put in

.save
.display

EDIT:

by the way I have a comment too.

Always set the outlook object to nothing last.

Set mailoutlook = Nothing

set all other outlook objects to nothing THEN

Set olapp = Nothing

I am making a wild guess but make sure that outlook is not still running in task manager and then run your code.

 

[wtrimble]

Ah hah, must have Outlook open for it to send the email!

 

[mikehershey]

You can open outlook from vba.

Dim olApplication As Outlook.Application
Set olApplication = CreateObject("Outlook.Application")

 

[darbid]

That's good that it works. Bit you don't need to be seeing outlook to send an email. It does however have to be a running process.

 

[wtrimble]

Yes, I have it all started up with a Schedule Task. Thanks

Anyway to pull the user name from Outlook or user login?? I want to greet the person when they open the database up

 

[wtrimble]

Yes I was opening only the Access database when the user logs in onto their computer through Scheduled Tasks. I was getting the problem because Outlook wasn't open yet. However I added Outlook to open on scheduled Tasks before it opens the Access database. Works fine as long as Outlook opens up before Access.

 

[mikehershey]

Environ$("Username")
That is the vba command to get the windows ID. IE my windows domain name

 

[wtrimble]

Yes that works. I also found this that finds it also. But I want to retrieve the full name of the person. When Outlook is opened, it automatically opens to the username of the user logged in (as in most cases). Anyway to pull the full name from Contacts or other area in outlook with knowing the user name??

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Sub Command0_Click()
 
' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        MsgBox Left$(strUserName, lngLen - 1)
    Else
        MsgBox "nothin"
    End If
 
 
   ' MsgBox Environ$("Username")
 
End Sub

 

[wtrimble]

like...
Dim olapp As Outlook.Application
Dim mailoutlook As Outlook.MailItem
Dim msg As String
Set olapp = CreateObject("outlook.application")
Set mailoutlook = olapp.CreateItem(olMailItem)

mailoutlook.sendername .. or some other property..