Solved

Send Email (Outlook 2010) from Access Table

Posted on 2014-04-03
12
694 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

776 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