Automated Email

[Marcinko]

Good Morning All -
I have an open orders report in my database that I use a Send Object macro to email off to each respective vendor I use. My question - How would I possibly make it so that the email automatically sends every 2 weeks? My goal is to have the open orders report go out every two weeks to my vendor to let them know of all overdue orders. I guess in the end maybe a timer function that will execute the macro every two weeks.

Thanks in advance for any help.

 

[fasternu]

An option would be to write a macro to send the object/query, then write a bat file to open access and execute the macro, and then utilize task scheduler in windows to execute the macro per your schedule.

 

[tmort]

Automated Emails

You can make a query to do the sendobject and then set up windows task scheduler to open the query directly using the /X switch.

In the task scheduler you would enter two paths:

The first is the path to microsoft access

the second is the path to the database and macro you want to run with the /X switch..

"c:\program files\microsoft office\office10\MSACCESS.EXE" "T:\folder name\db name.mdb" /x macro name

However, if you move on to a newwer version of Outlook you may find that a security feature that requires a prompt before automatically sending email thwarts your attempts to automate this unattended.

That is where I'm at.

I'm going to do a new post, but I'm thinking I can set up a macro to close outlook, open another mail program that is MAPI compliant (Pegasus, its free) do the sendobject command and then close pegasus and reopen Outlook to return things to normal and then close Access. I'm not sure how to close Outlook or the other program with VBA

 

[JeffreyDavid]

SendObject not available

Hi guys,
I have been trying to setup Automated Email on a form and have had no luck. I saw the post about creating a macro to send mail, so I tried it and came the closest yet, but still got an error. My form has a bunch of text boxes, couple of combo boxes and a save button. I've got the macro set to run when the save button is clicked. It tells me that SendObject is not available at this time. I reinstalled Access but same result.

Any ideas?

 

[tmort]

Auto email

You mention what the form contains, are you trying to email the form? Is so I don't think sendobject would work with a form as the form would no longer function.

If its the contents of the form you want to send you could set up a report with the same information and use the sendobject command to send the report linked to the button on the form.

 

[cavscout]

Here is a little Detail Request Screen I use to allow employees to email a request to me and also add them to a log. I show a lot of carriage returns (vbCr) for formatting my output.

'Email Notice
Dim strList As String
Dim intcounter As Integer
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String

For intcounter = 0 To Me.ProductsList.ListCount - 1
Me.ProductsList.Selected(intcounter) = True
Next intcounter

For intcounter = 0 To Me.ProductsList.ListCount - 1
If Me.ProductsList.Selected(intcounter) Then
strList = strList & Me.ProductsList.Column(0, intcounter) & vbCr
End If
Next intcounter
strList = Left(strList, Len(strList) - 1)

Me.ProductsList = strList


Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail

.To = "Gomez Adams" 'Could also be anyone@hotmail.com
.CC = Text41 '.Value 'I used this to send copy to employee
'.BCC = "Department Manager"
.Subject = Text41 & " entered a Detail Requisition for " & Agent & "."
.Body = Text41 & _
" has requested a detail('s) for " & _
vbCr & vbCr & _
Agent & "," & _
vbCr & vbCr & _
"for the following systems:" & _
vbCr & vbCr & _
ProductsList & _
vbCr & vbCr & _
"Comments: " & Comments & _
vbCr & _
"Request #: " & ID & _
vbCr & _
"Project Name: " & ProjectName & _
vbCr & _
"Requested By: " & CompanyRequestedBy & _
vbCr & _
"Requested Date: " & DateReq & _
vbCr & _
"Location: " & City & " , " & State

'.Attachments.Add CurrFile
'.Attachments.Add "c:\My Documents\book.doc"
.ReadReceiptRequested = True
'.PrintOut
.Send

End With

Set olMail = Nothing
Set olApp = Nothing

I put this behind a submitt button and it works fine in A97.

Makes for a nice clean email. Hope it helps.