UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Birthday Greeting On Email, Access 2016    
 
   
akbarmahfuzalam
post Jan 16 2020, 07:40 AM
Post#1



Posts: 42
Joined: 8-December 19



I have a database that is located on a shared path. The database has all the necessary detail of an employee in a table form.

I would like to add a feature where the Access program will automatically send an email message with an image(stored in the shared path) to certain addresses when a deadline has not been met.

We were doing the same thing but in MS office. We are using MS Access 2016.

Any assistance that you can give will be appreciated.
This post has been edited by akbarmahfuzalam: Jan 16 2020, 07:42 AM
Go to the top of the page
 
ADezii
post Jan 16 2020, 08:14 AM
Post#2



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Are you using Outlook as your E-Mail Client?
  2. Do you wish to Embed the Image in the E-Mail or Attach it?
Go to the top of the page
 
projecttoday
post Jan 16 2020, 09:00 AM
Post#3


UtterAccess VIP
Posts: 11,594
Joined: 10-February 04
From: South Charleston, WV


You can schedule a a task with the Windows scheduler that starts an Access macro that will run once a day or month. So first you have to create the code that searches for the overdue accounts and creates emails and then you set up the task that executes it.

--------------------
Robert Crouser
Go to the top of the page
 
akbarmahfuzalam
post Jan 22 2020, 01:57 AM
Post#4



Posts: 42
Joined: 8-December 19



Yes, I am using Microsoft Outlook and need to send images with Signature on the bottom.
Go to the top of the page
 
ADezii
post Jan 22 2020, 10:43 AM
Post#5



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Let's tackle your problem one aspect at a time. The first thing to achieve is to send a Customized E-Mail to all those Employees who have not met a Dateline.
  2. To simulate this request I have created a simple Table with only five Fields. One of these Fields [Dateline Met] will determine which Employees will receive an E-Mail ([Dateline Met]=False).
  3. This is accomplished by opening an Instance of Outlook (Late Binding), creating a Recordset filtered for only those Employees who have not met a Dateline, and sending each of them a Customized E-Mail.
  4. Table (Employees) Data:
    IDLast NameFirst NameE-mail AddressDeadline Met
    1FreehaferNancynancy@northwindtraders.comTrue
    2CenciniAndrewandrew@northwindtraders.comFalse
    3KotasJanjan@northwindtraders.comTrue
    4SergienkoMariyamariya@northwindtraders.comTrue
    5ThorpeStevensteven@northwindtraders.comTrue
    6NeipperMichaelmichael@northwindtraders.comFalse
    7ZareRobertrobert@northwindtraders.comFalse
    8GiussaniLauralaura@northwindtraders.comTrue
    9Hellung-LarsenAnneanne@northwindtraders.comFalse
  5. Code Definition:
    CODE
    Dim oLook As Object
    Dim oMail As Object
    Dim olns As Object
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM Employees WHERE [Deadline Met] = False"

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

    Set oLook = CreateObject("Outlook.Application")
    Set olns = oLook.GetNamespace("MAPI")

    Do While Not rst.EOF
      Set oMail = oLook.CreateItem(0)
        With oMail
          .To = rst![E-mail Address]
          .Body = "Wishing a very Happy Birthday to " & rst![First Name] & " " & _
                   rst![Last Name]
          .Subject = "Birthday Greetings From <Your Company Name Here>!"
            .Send
        End With
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set MyDB = Nothing
    Set oLook = Nothing
    Set oMail = Nothing
    Set olns = Nothing
  6. Once you fully understand the Logic and Code, then we can hopefully move on to the Embedded Graphic and Signature.
Go to the top of the page
 
akbarmahfuzalam
post Jan 27 2020, 01:31 AM
Post#6



Posts: 42
Joined: 8-December 19



Hi,
Cool Understood the sequence. Proceed further please
Go to the top of the page
 
ADezii
post Jan 27 2020, 10:01 AM
Post#7



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Here is where things start getting a little complicated. When you create a Signature in Outlook, it is stored in three different formats (*.htm, *.rtf, *.txt) in the \Microsoft\Signatures\ Folder of the AppData Path which can be found in the Environmental Variable AppData. If you create a Signature named Acme Industries, it's Files can be found in:
    CODE
    Environ("appdata") & "\Microsoft\Signatures\Acme Industries.rtf"
    Environ("appdata") & "\Microsoft\Signatures\Acme Industries.htm"
    Environ("appdata") & "\Microsoft\Signatures\Acme Industries.txt"
  2. To now incorporate a Signature and to embed a Graphic File into E-Mails, you ned to use the HTMLBody Property along with a couple of specialized Tags.
  3. The following Code will send E-Mails to all Employees where [Deadline Met] = False. The Body of the E-Mail will consist of pre-defined Text plus the Acme Industries Signature plus the Company Logo (Logo.jpg).
    CODE
    Dim oLook As Object
    Dim oMail As Object
    Dim olns As Object
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strBody As String
    Dim strSignature As String
    Dim strGraphic As String

    Open (Environ("appdata") & "\Microsoft\Signatures\Acme Industries.htm") For Input As #1

    strSQL = "SELECT * FROM Employees WHERE [Deadline Met] = False"
    strSignature = Input(LOF(1), #1)
    strGraphic = "C:\Pics\Logo.jpg"

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

    Set oLook = CreateObject("Outlook.Application")
    Set olns = oLook.GetNamespace("MAPI")

    Do While Not rst.EOF
      Set oMail = oLook.CreateItem(0)
        With oMail
          .To = rst![E-mail Address]
           strBody = "Wishing a very Happy Birthday to " & rst![First Name] & " " & _
                      rst![Last Name]
          .HtmlBody = strBody & "<br><br>" & strSignature & "<br>" & "<IMG src=" & strGraphic & ">"
          .Subject = "Birthday Greetings From <Your Company Name Here>!"
            .Send
        End With
        rst.MoveNext
    Loop

    Close #1

    rst.Close
    Set rst = Nothing
    Set MyDB = Nothing
    Set oLook = Nothing
    Set oMail = Nothing
    Set olns = Nothing
  4. The Code has been tested and is fully operational. Obviously, substitute you own Values for the Variables and Paths.
Go to the top of the page
 
akbarmahfuzalam
post Jan 28 2020, 07:11 AM
Post#8



Posts: 42
Joined: 8-December 19



Hi,

I have gone through Code. But unfortunately i am unable to showcase the HTML Images on the body of the email.

Sub emailbirthday()
Dim oLook As Object
Dim oMail As Object
Dim olns As Object
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strBody As String
Dim strSignature As String
Dim strGraphic As String

Open (Environ("appdata") & "\Microsoft\Signatures\Acme Industries.htm") For Input As #1

strSQL = "SELECT * FROM Employees WHERE [Deadline Met] = False"
strSignature = Input(LOF(1), #1)
strGraphic = "C:\Pics\Logo.jpg"

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")

Do While Not rst.EOF
Set oMail = oLook.CreateItem(0)
With oMail
.To = rst![E-mail Address]
strBody = "Wishing a very Happy Birthday to " & rst![First Name] & " " & _
rst![Last Name]
.HtmlBody = strBody & "<br><br>" & strSignature & "<br>" & "<IMG src=" & strGraphic & ">"
.Subject = "Birthday Greetings From PDO Automation Team!"
.Display
'.Send
End With
rst.MoveNext
Loop

Close #1

rst.Close
Set rst = Nothing
Set MyDB = Nothing
Set oLook = Nothing
Set oMail = Nothing
Set olns = Nothing

End Sub


Note: Is there any other format can we embedded and what would be the code. strGraphic is not capturing in the body of the email.
Go to the top of the page
 
ADezii
post Jan 28 2020, 09:20 AM
Post#9



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


You need to change the Path of the Graphic to your own, NOT C:\Pics\Logo.jpg, which is for illustration only.
Go to the top of the page
 
akbarmahfuzalam
post Jan 29 2020, 01:15 AM
Post#10



Posts: 42
Joined: 8-December 19



Hi ADezii,

Thanks for your quick response and assistance. I would like to bring to your kind notice that even after changing the path. I am not able to view the Images on the email body.

Please find the attachment for your Reference.

Your help is highly appreciated.

Thanks in Advance.
This post has been edited by akbarmahfuzalam: Jan 29 2020, 02:00 AM
Attached File(s)
Attached File  Error.PNG ( 158.85K )Number of downloads: 9
 
Go to the top of the page
 
isladogs
post Jan 29 2020, 02:47 AM
Post#11


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


Please read the image related parts of this similar thread at another forum.
That may explain your issue with images not showing.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
akbarmahfuzalam
post Jan 29 2020, 06:29 AM
Post#12



Posts: 42
Joined: 8-December 19



Hi Colin,

I have gone through the thread but my bad luck.
Go to the top of the page
 
isladogs
post Jan 29 2020, 08:20 AM
Post#13


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


Sorry. Don't understand your response.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
akbarmahfuzalam
post Jan 29 2020, 09:26 AM
Post#14



Posts: 42
Joined: 8-December 19



Hi Colin,

I means to say I have gone through a similar thread which you have recommended me but I couldn't find similarity with my problem.
Go to the top of the page
 
ADezii
post Jan 30 2020, 09:02 AM
Post#15



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


Bear with me and perform the following simple steps:
  1. Download the attached Graphic File (Logo.jpg).
  2. Copy this File to the 'same Folder' as your Database.
  3. Replace the following line of Code
    CODE
    strGraphic = "Y:\Akbar\Birthday MS Access\Images\Logo.jpg"
  4. with
    CODE
    strGraphic = CurrentProject.Path & "\Logo.jpg"
  5. Execute the Code and let me know what happens.

This post has been edited by ADezii: Jan 30 2020, 09:04 AM
Attached File(s)
Attached File  Logo.zip ( 23.31K )Number of downloads: 6
 
Go to the top of the page
 
akbarmahfuzalam
post Jan 30 2020, 09:26 AM
Post#16



Posts: 42
Joined: 8-December 19



Hi ADezii,

No image found only text message. Do you want me send the database to your personal Email ID.
Attached File(s)
Attached File  Error.PNG ( 128.53K )Number of downloads: 8
 
Go to the top of the page
 
ADezii
post Jan 30 2020, 12:23 PM
Post#17



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


Upload your Database making absolutely sure that there is no sensitive information contained within it.
Go to the top of the page
 
akbarmahfuzalam
post Jan 31 2020, 05:29 AM
Post#18



Posts: 42
Joined: 8-December 19



ADezii,

Please find the attached database for your REF.

Let me know where i went wrong.

Attached File(s)
Attached File  Birthday_Copy.zip ( 1.04MB )Number of downloads: 8
 
Go to the top of the page
 
ADezii
post Jan 31 2020, 08:11 AM
Post#19



Posts: 2,759
Joined: 4-February 07
From: USA, Florida, Delray Beach


The Code functions exactly as intended. Are you sure that Logo.jpg is in the 'same Folder' as your Database?
CODE
strGraphic = CurrentProject.Path & "\Logo.jpg"
Go to the top of the page
 
akbarmahfuzalam
post Jan 31 2020, 08:28 AM
Post#20



Posts: 42
Joined: 8-December 19



ADezii,

I pasted it in the same folder. Are you able to use the same in your system?


Attached File(s)
Attached File  Capture.JPG ( 31.12K )Number of downloads: 3
 
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 08:00 PM