Solved Automate mails (1 Viewer)

[Ravi Kumar]

Dear all ,

I have a table(name:Tasks) which has 4 columns:

1. Date
2. Task
3. Status(pending/completed)
4. Responsible person(email link)

I need to send a mail to everyone only once per week if the task is pending.(Mondays to be precise)
Currently I am using a report which filters only pending & then I am sending the mail using docmd object.
Does anyone has a vba code to automate this through a button which I can place in my main menu form?

 

[GinaWhipp]

Hmm, this should help...

Access Diva Automatically Send eMail Notifications

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[Ravi Kumar]

Thank you/\.

 

[Ravi Kumar]

Hello I looked at the article & another which had same topic & with somemodifications , it is sending a mail for only first row .
But I need it to send a separate email with the same attachment( as it contains all details) for the person assigned .How to achieve this?

Private Sub cmdtrial_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryeMailOverdueTasks")
With rs
If .EOF And .BOF Then
MsgBox " No emails will be sent because there are no records"
Else
Do Until .EOF
DoCmd.SendObject acSendReport, "Tasks", pdf, [responsible person], "Prabha.Tirupatiananthachar@plansee.com", , "Task Overdue", _
"The task related to " & ![Issue] & "," & "is pending" & "," & "kindly complete as soon as possible"
.MoveNext
Loop
End If
End With
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
End Sub

 

[Ravi Kumar]

Can anyone help me with this please
I set the edit to false in docmd object ...but still I have 2 issues now
1 the mails sent is not storing in my sent mails folder
2 and the second row is not looping properly.. sometimes working and sometimes not..

 

[GinaWhipp]

The link I posted tells you how to send each person their copy. Since it sounds like you adjust the code to suit you need to post what you are using now that is not working.

Side note, this is a volunteer Forum,. We can only reply when we have time (and are awake, time zones), so please be patient.

 

[Ravi Kumar]

The link I posted tells you how to send each person their copy. Since it sounds like you adjust the code to suit you need to post what you are using now that is not working.

Side note, this is a volunteer Forum,. We can only reply when we have time (and are awake, time zones), so please be patient.

Thanks for your time...
I didn't used the code from the Link provided..but used from an another Link... found while searching..
I just needed to know what's wrong I made it in the above code... sorry again for rushing..

 

[GinaWhipp]

Well, the first thing that jumps out is...

        .MoveFirst  "<<< you are not sending it to the first record in the loop
            Do While Not .EOF  '<<< at which point you can loop to the end of the recordset

Then you are not saving the report before sending the eMail...

DoCmd.OutputTo acOutputReport, "YourReport", acFormatPDF, "Drive:\Folder\" & !YourPK& "-YourNameForReport.pdf"

So, you need to adjust that code accordingly.

 

[pbaldy]

Gina, have you found the MoveFirst to be necessary? I never put it before a loop, unless I've already moved the recordset for something else.

 

[GinaWhipp]

Gina, have you found the MoveFirst to be necessary? I never put it before a loop, unless I've already moved the recordset for something else.

I do for this. For whatever reason when I took it out I did not get all the people in the recordset. And, I always thought one day I would get back to it to see why but then I figured, it worked so move on.

 

[pbaldy]

Maybe I'm just lucky, although I typically loop with

Do While Not rs.EOF

rather than Do Until, though I can't see how that would make a difference.

 

[GinaWhipp]

Maybe I'm just lucky, although I typically loop with

Do While Not rs.EOF

rather than Do Until, though I can't see how that would make a difference.

Or MAYBE I was playing so much and left it there because what I tried earlier needed it. Like I said always meant to go back to that and NOW you have me intrigued and I know what I'm playing with this weekend.

I only in the past few years switched to Do While Not rs.EOF finding it more efficient. I guess I'm a slow learner.

 

[pbaldy]

I wish I could say I started using Do While Not after extensive testing proved it to be more efficient, but it's just what I learned first.

 

[GinaWhipp]

Unfortunately (or fortunately) self taught so I didn't learn that, among other things, without testing with many failures. Or, as I sometimes say, playing with code.

 

[Ravi Kumar]

Hello all ,
With all your help & searching in google the code is completely working .
But I am struck where to add this output object in the code & attach it in my mail & also how to auto delete from my desktop after sending.
kindly look at the code:

Option Compare Database
Option Explicit

Public Sub sendserialemail()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim emailto As String
Dim emailcc As String
Dim emailsubject As String
Dim emailtext As String
Dim outapp As Outlook.Application
Dim outmail As Outlook.MailItem
Dim outstarted As Boolean
Dim rpt As Report

On Error Resume Next
Set outapp = GetObject(, "outlook.application")
On Error GoTo 0
If outapp Is Nothing Then
Set outapp = CreateObject("outlook.application")
outstarted = True
End If

Set db = CurrentDb
Set rs = db.OpenRecordset("qryeMailOverdueTasks")
Do Until rs.EOF
emailto = rs.Fields("responsible person").Value
emailcc = "ravikumar.ramadas@plansee.com"
emailsubject = "Pending tasks to complete the issue regarding" & " " & rs.Fields("issue").Value
emailtext = "Hello" & vbCrLf & "Kindly complete the task which is in the attached file to complete the cutomer isue regarding" & " " & rs.Fields("issue").Value

Set outmail = outapp.CreateItem(olMailItem)
outmail.To = emailto
outmail.CC = emailcc
outmail.Subject = emailsubject
outmail.Body = emailtext
outmail.Send

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

If outstarted Then
outapp.Quit
End If

Set outmail = Nothing
Set outapp = Nothing

End Sub

 

[The_Doc_Man]

Regarding attachments:

Attach a File to a Mail Item

Office VBA reference topic

docs.microsoft.com

Attachments.Add method (Outlook)

Office VBA reference topic

docs.microsoft.com

Regarding removing an item from the desktop after sending it:

In order to send the item as an attachment, you have to have its path in order to use the Attachment.Add method. Outside the loop you could simply use the VBA "KILL" verb. Or if you already were using the File System Object, there is a File.Delete method. Either approach would work. The key is finding the object to be sent in the first place. However, if it is on the desktop, that acts like a folder including programmed file deletion.

 

[Ravi Kumar]

Thank you ,
I made the necessary changes as you said , the file is automatically updates every I am sending the mail as it is in my documents folder.
The issue is now solved.

 

[The_Doc_Man]

Great! Glad I could contribute a push in the right direction.