Solved

Send Email (Outlook 2010) from Access Table

Posted on 2014-04-03
12
685 Views
Last Modified: 2014-04-03
I need to send emails as a group from a Access table with attached Excel spreadsheet on a command button.
0
Comment
Question by:shieldsco
  • 7
  • 4
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39975143
try this codes


Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath as string
xlPath = "c:\folderName\myexcel.xls"


Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
         .Subject = sSubj
      .Attachments.Add xlPath

      .display
      '      .send
End With


to get all the recipient emails, open the the table as recordset

dim rs as dao.recordset
set rs=currentdb.openrecordset("select email from tableName")

do until rs.eof
     sMail=sMail & ";" & rs("email")
    rs.movenext
loop

sMail=mid(sMail,2)
0
 

Author Comment

by:shieldsco
ID: 39975707
It opens Outlook and attaches file however the code does no populate the email address in Outlook
0
 

Author Comment

by:shieldsco
ID: 39975951
Email address is not populated in Outlook
0
 

Author Comment

by:shieldsco
ID: 39975965
Does the email field have to text? Currently I have it as a hyperlink
0
 

Author Comment

by:shieldsco
ID: 39975967
Below is the code

Private Sub Email_Click()


Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath As String
xlPath = "\\cdc\project\OD_FMO_DEBT_MGT\PA Invoice Split Database\Email\Invoice_Email.xlsm"





Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
         .Subject = sSubj
      .Attachments.Add xlPath

      .display
      '      .send
End With



Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblStaff")

Do Until rs.EOF
     sMail = sMail & ";" & rs("email")
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)




End Sub
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39976111
use this codes


Private Sub Email_Click()
Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath As String
xlPath = "\\cdc\project\OD_FMO_DEBT_MGT\PA Invoice Split Database\Email\Invoice_Email.xlsm"

Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblStaff")

Do Until rs.EOF
     sMail = sMail & ";" & rs("email")
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)




Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
         .Subject = sSubj
      .Attachments.Add xlPath

      .display
      '      .send
End With

End Sub


.
0
 

Author Comment

by:shieldsco
ID: 39976146
The code works fine however the TO : line in the outlook message is this format Adrianne.Thomas@va.gov#mailto: Adrianne.Thomas@va.gov# which outlook does not recognize. Any thoughts on fixing the problem
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39976180
dim varEmail as string

Do Until rs.EOF
varEmail=rs("email")
     sMail = sMail & ";" & Left(varEmail, Instr(varEmail,"#")-1)
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)
0
 

Author Comment

by:shieldsco
ID: 39976215
I get run time error 94 invalid use of null on code:
varEmail = rs("email")
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39976253
dim varEmail as string

Do Until rs.EOF
  if rs("email") & ""<>"" then
       varEmail=rs("email")
           sMail = sMail & ";" & Left(varEmail, Instr(varEmail,"#")-1)
   end if
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)
0
 

Author Closing Comment

by:shieldsco
ID: 39976270
Thanks for ALL YOUR HELP!!!!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access MDB/PDF 21 32
Max per month 3 15
Code editor Problem 8 16
SQL Query 3 0
Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now