Email report in body of message Results

Hello,

I have a very simple report that I need to have in the body of an email. I have a form with a button that should activate outlook, add the simple report to the body of the email and send. The TO: field will be blank due to a distro list. The subject field should also have a standard constant message.

The report is updated in access so it doesn't need to be done in this code. I need this to be very simple. I am having code-writing-block. Please assist with this.

I would also like to have the format be txt not html or snapshot.

thank you all for the assistance.

here is my code


Dim strMsgBody As String
strmsgbody = ??????

DoCmd.OpenReport "Daily_Text", acViewPreview, "", "", acNormal
DoCmd.SendObject acReport, "Daily_Text", "SnapshotFormat(*.snp)", "", "", "", "LNP Totals", strMsgBody, True, ""
MsgBox "Text Message report has been processed.", vbInformation, ""

I have 2 issues with a report that I am trying to send in the body of an email to different recipients:
First, I need to export the report to the body of an outlook email message. The report is very simple text with no tableslines or anything like that, so there is no real need for HTML formatting.
Second, I would like the "To:" field in outlook to be populated from an "email" field on a form that is open when the "send email" button is clicked. In other words, I want Access to pass the contents of a field to the "To:" line in outlook, somehow.
I am a new Access user with practically NO coding experience: please be gentle.

Thanks for any help.

Hi,
Iím using access 2007. I would like to use one of my reports as the body of an outlook 2007 email message. Iím able to export the report as an HTML file but canít figure out how to use either as body of the email message. Is this possible in VBA? Can anyone help with an example or sample code if possible?

Thanks All

Cris

I am trying to send a report in the body of an e-mail rather then by attachment. Reason being too many complaints about the attachments; extra steps to view, looking distorted when viewed on small screens, etc..)
The following are two codes I have modified to use however I get errors on both of them. The top code sends false or 0 in the body instead of the data. And the second either gives me an error on the .Send line, but if I remove it clicking the button does nothing.


	Code:
	Private Sub Email_Click()
Dim olApp As Object
Dim olMail As Object
Dim StMailBody As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)

With olMail
.To = ""
.Subject = ([ProductionOrProcessing] & " Shift Report " & [ID])
.HtmlBody = .HtmlBody = "Safety" < br > Me.Safety < br > "Quality:  &< br > Me.Quality"
.Display
End With

Set olMail = Nothing
Set olApp = Nothing
End Sub


	Code:
	Private Sub Email2_Click()
Dim mess_body As String
Dim olApp As Object
Dim olMail As Object
Dim StMailBody As String
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)


StMailBody = StMailBody = "Safety" < br > Me.Safety < br > "Quality: " < br > Me.Quality

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.To = ""
.Subject = ([ProductionOrProcessing] & " Shift Report " & [ID])
.Body = StMailBody
.Send = ""
End With

Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:

Set olMail = Nothing
Set olApp = Nothing
End Sub

I have been searching the threads for awhile and I know this is possible but I have not been able to find the answer that helps me yet.

I have a user form for supplier issues and a button that captures the data into the body of an email. I'd like to be able to format the message in the email with returns and such to make it more readable.

The user enters the problem then clicks the update button which has a simple send no object email behind it.

My thought was to us ascii Carriage Return which according to the lookup table is Dec:13 Hx: D Oct:015 Char:CR. I don't know how to go about putting this in the body of the email message code.

I'd like not to have to create a report to send as an attachment but don't want them to receive a random string of info bits or a run together paragraph to decipher.

Help would be appreciated, other suggestions welcome.

Thank you in advance.

I have several reports that I have made in access (2010) with it's reporting tool. What I would like to do is to send each of these reports off in an email to there corresponding departments. Sounds simple enough already huh?? Well I have the email function in place so that is finished but now what is happening is when I try to save the reports off in pdf format so they can be attached to the emails. I get a run time error, a "2046 The command or action 'Output To' isn't available now. I attempted to solve this by putting a "DoEvents" after the OutputTo and it continued to throw the same error. I then proceeded to make a timer function to cause the subroutine to "pause" for a few seconds and it worked for the first report but when the second report tried to produce BANG the same error.

For some background on what I am trying to accomplish here. I have a table that contains the PersonnelName, email, cc, subject respectively and are all text fields. The code calls a recordset to "lookup" the respective fields and produce an email with the report attached. I am not so worried about saving the report as attaching it to the email to be sent out as this is done daily. Oh I should probably mention this is all done by clicking a command button. So how would I go about producing the pdf for attachment?

My code as it stands now is as follows:
Code: Private Sub cmdSendReports_Click() Dim MySet As DAO.Recordset Set MySet = CurrentDb.OpenRecordset("SELECT * FROM tblEmailAdd") Do Until MySet.EOF Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment 'Call function to start timer for file to finish Pause (12) 'Output report to pdf file DoCmd.OutputTo acOutputReport, MySet!ReportName, acFormatPDF, "C:usersmgayDesktop" & "" & MySet!ReportName & ".pdf" DoEvents DoCmd.Close acReport, MySet!ReportName 'Create the outlook session Set objOutlook = CreateObject("Outlook.Application") 'Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) With objOutlookMsg 'Add Recipient to the message Set objOutlookRecip = .Recipients.Add(MySet!email) objOutlookRecip.Type = olTo 'Add the CC Recipient to the message Set objOutlookRecip = .Recipients.Add(MySet!CC) objOutlookRecip.Type = olCC 'Add the BCC Recipient to the message Set objOutlookRecip = .Recipients.Add("mgay@nativeoilfield.com") objOutlookRecip.Type = olBCC 'Set the Subject, Body and Importance of the message. .Subject = MySet!Subject .Body = "Daily Error Report for " & Date .Importance = olImportanceNormal 'Add Attachments to the message If Not IsMissing(MySet!ReportName) Then Set objOutlookAttach = .Attachments.Add("C:UsersmgayDesktop " & MySet!ReportName & ".pdf") End If 'Resolve each recipient's name. For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve Next 'Should we display the message before we send it? If DisplayMsg Then .Display Else .Send ' .Save End If End With Set objOutlook = Nothing MySet.MoveNext Loop End Sub
The function called above is as follows:

Code: Public Function Pause(NumberOfSeconds As Variant) On Error GoTo Err_Pause Dim PauseTime As Variant Dim Start As Variant PauseTime = NumberOfSeconds Start = Timer Do While Timer < Start + PauseTime DoEvents Loop Exit_Pause: Exit Function Err_Pause: MsgBox Err.Number & " _ " & Err.Description, vbCritical, "Pause()" Resume Exit_Pause End Function
I know this is a lot but I have been working for awhile on this. Could someone please offer some insight

Thank you in advance,

Max

Hi, currently I have a form set up to automatically open and send an email to myself and then the dbase closes. I have two fields in the form [recipient] and [emaildate] each of which are contingent on when and to whom the email is sent. This will occur every day as i have the current date (=Now) in one of the [emaildate] field. I use the code below to accomplish this.



Private Sub ReminderDate_Exit(Cancel As Integer)
Dim appOutLook As Object
Dim MailOutLook As Object

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = Me.Recipient 'get the recipient from the form
.Subject = "This is a reminder email"
.body = "the message goes here e.g. remember to make the call"
.DeferredDeliveryTime = Me.ReminderDate 'get the date from the form

.Send
End With

Set appOutLook = Nothing
Set MailOutLook = Nothing
End Sub


So currently the email sends a few words in text and everything works fine. What i want to do is send a report through email in PDF format. Lets assume that i have a report called [Report1]. how can i get this same scenario to work.

Thanks any quick help will be appreciated. I want to present something tomorrow.. thanks

I have the following code that gets data from a query then will send an email to roughly 150 people. I want them to reply and verify the information that I have in the database.

What I would like is for them to respond easily. I was wondering if it was possible to put 2 buttons in the body of the email? one button that replies to the message with text that says "all information is complete and valid", and a second button that would essentially just click reply - then they could just respond whatever way they wanted. I'm thinking most will hit the "All is OK" button, so I would like that as automated as possible so I'm not waiting forever for people to respond.

Or is there some easier way that I'm missing. And/or is it even possible to put that kind of button in an outlook email?

here's the code I have:


Private Sub cmdSendEmail_Click()

Dim objOutlook As Object
Dim objMailItem As Object

Dim strSubject As String
Dim strFrom As String
Dim strBody As String
Dim intJV As Integer
Dim rstEmail As DAO.Recordset

'LOOP THROUGH A RECORDSET TO SEND AN EMAIL TO EACH RECORD
Set rstEmail = CurrentDb.OpenRecordset("qryEmailRegistration")
rstEmail.MoveLast
rstEmail.MoveFirst

intJV = 0
For intJV = 0 To rstEmail.RecordCount - 1

'EMAIL FROM HERE

strFrom = """School Records """

'EMAIL SUBJECT HERE

strSubject = "School Information Verification"

'EMAIL BODY HERE
strBody = "To The " & rstEmail!FamilyLastName & " Family:" & vbCrLf & vbCrLf & "As you may know, the school has implemented a new school database." _
& " This database will aid in automating the school directory, school mailings, the One Call Nowô system, and various other reports and office records containing the student and family information." _
& vbCrLf & vbCrLf & "It is important that the information be accurate, so we ask that you please verify the details below that are currently in the database. Please reply to this email with any changes/updates." _
& vbCrLf & vbCrLf & "Thank you." & vbCrLf & vbCrLf & "Please respond to this email ASAP. " _
& vbNewLine & "* * * * * * * * * * * * * * * * * * * *" _
& vbNewLine & "Information on file for the " & rstEmail!FamilyLastName & " Family..." & vbCrLf & vbCrLf & vbTab & "Primary Address: " & rstEmail!FamilyStreetAddress & " " & rstEmail!FamilyCity & ", " & rstEmail!FamilyZipcode & vbCrLf & vbCrLf & vbTab & "Primary Phone Number: " _
& vbTab & rstEmail!PrimaryTelephone & vbCrLf & vbCrLf & vbTab & "Mother's Name: " & rstEmail!MotherFirstName & vbCrLf & vbCrLf & vbTab & "Mother's Work Phone: " & rstEmail!MotherWorkNo & vbCrLf & vbCrLf & vbTab & "Mother's Cell: " _
& rstEmail!MotherCellNo & vbCrLf & vbCrLf & vbTab & "Mother Additional eMail: " & rstEmail!MotherEMail2 & vbCrLf & vbCrLf & vbTab & "Father's Name: " & rstEmail!FatherFirstName & vbCrLf & vbCrLf & vbTab & "Father's Work: " _
& rstEmail!FatherWorkNo & vbCrLf & vbCrLf & vbTab & "Father's Cell: " & rstEmail!FatherCellNo & vbCrLf & vbCrLf & vbTab & "Father Additional eMail: " & rstEmail!FatherEMail2 _
& vbNewLine & vbNewLine & "* * * * * * * * * * * * * * * * * * * *" _
& vbNewLine & "Your Primary Address and Primary Telephone Number ONLY will be published in the directory. Please let me know if you do not want your information listed." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & Chr(13) & Chr(10)

Set objOutlook = CreateObject("Outlook.application")
Set objMailItem = objOutlook.CreateItem(0)

With objMailItem
.To = rstEmail!MotherEMail
.SentOnBehalfOfName = strFrom
.SUBJECT = strSubject
.Importance = 2
.Body = strBody

.Display
SendKeys "%{s}", True
End With

Set objMailItem = Nothing
Set objOutlook = Nothing

rstEmail.MoveNext
Next intJV

End Sub

Thank you!!

Hey All,

Trying to send an email with an attachment. I keep getting this error
"Can't find this file. Make sure the path and file name are correct" They are correct I am using a code sample I found on the forum.

I'm stumped. HELP

Thanks
Rich

Here's the code.

Option Compare Database
Option Explicit
' You need to declare a reference to the Outlook library, and the filesystemobject.
' this is not as hard as it sounds.
'
' Look in the menu above, and click Tools, then select References
'
' Scroll down the list until you see
' Microsoft Scripting Runtime -- and put a check next to it (if one is not there already)
'
' Microsoft Outlook Object Library -- check that.
' There will be some version number there as well; it doesn't matter.
' This will work with Outlook98 and Outlook2000 and OutlookXP. It hasn't been tested on Outlook 2003 yet.


Public Function SendEmail()


Dim MailList As String
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

' First, we need to know the subject.
' We can't very well be sending around blank messages...

Subjectline$ = "Accurate Public Records, LLC."
' If there's no subject, call it a day.

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If

' Now we need to put something in our letter...

'BodyFile$ = "C:Proposal.Txt"

' If there's nothing to say, call it a day.

'If BodyFile$ = "" Then
'MsgBox "No body, no message." & vbNewLine & vbNewLine & _
' "Quitting...", vbCritical, "I Ain't Got No-Body!"
'Exit Function
'End If

' Check to make sure the file exists...
'If fso.FileExists(BodyFile$) = False Then
'MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "I Ain't Got No-Body!"
'Exit Function
'End If

' Since we got a file, we can open it up.
'Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)

' and read it into a variable.
MyBodyText = "This Is A Test"

' and close the file.
'MyBody.Close

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application


' This creates the e-mail

Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = [Forms]![Marketing]![Email]

'This gives it a subject
MyMail.Subject = Subjectline$

'This gives it the body
MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

MyMail.Attachments.add ("C:APR DocsAPR Application"), olByValue, 1, "Apr Application"
'MyMail.Attachments.add "c:dbgout.txt", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don't want the attachment's icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"



'This sends it!
'MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

MyMail.Display

'Cleanup after ourselves

Set MyMail = Nothing


'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

End Function

Hi, me again!

I have the following code that pulls the logging users area, team, manager and email so that it can notify them of the error that has been logged.

It all works, which is good however it is sending the email twice!!

I have tested the SQL in Query Design and it only pulls one row of date (which is expected in this sql statement) however its sending the email twice.

Code as follows:

'Sub routine that sends an email to the team causing manager(s) when a breach is logged.
Public Sub SendBreachEmail()
On Error GoTo ErrSendBreachEmail:
Dim LoggedArea1 As String
Dim LoggedArea2 As String
Dim LoggedTeam1 As String
Dim LoggedTeam2 As String

LoggedArea1 = Form_frmLogBreach.cboAreaCaused1
LoggedArea2 = Nz(Form_frmLogBreach.cboAreaCaused2, "")
LoggedTeam1 = Form_frmLogBreach.cboTeamCaused1
LoggedTeam2 = Nz(Form_frmLogBreach.cboTeamCaused2, "")

Dim sql As String
Dim rec As dao.Recordset

'Assigns the right SELECT statement to sql depending on the number of team(s)/area(s) causing.
If IsNull(Form_frmLogBreach.cboAreaCaused2) Then
sql = "SELECT tblUsers.FullName, tblUsers.Email FROM tblUsers WHERE (((tblUsers.UserGrade)='Manager') AND ((tblUsers.Department)='" & LoggedArea1 & "') AND ((tblUsers.Team)='" & LoggedTeam1 & "'));"
Else
sql = "SELECT tblUsers.FullName, tblUsers.Email FROM tblUsers WHERE (((tblUsers.UserGrade)='Manager') AND ((tblUsers.Department)='" & LoggedArea1 & "') AND ((tblUsers.Team)='" & LoggedTeam1 & "')) OR (((tblUsers.Department)='" & LoggedArea2 & "') AND ((tblUsers.Team)='" & LoggedTeam2 & "'));"

End If

Set rec = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
'rec.MoveFirst
'Loops through each email address returned, sending an email to each.
Do Until rec.EOF


'MsgBox (rec!Email & " " & rec!FullName)
'Sends the actual email.
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = rec!Email
.From = "Error Reporting Tool"
.Subject = "AUTOMATED MESSAGE: A breach has been logged against your team."
.Body = "Dear " & rec!FullName & vbCrLf & vbCrLf & "An error has been logged against your team. Here is the summary:" & vbCrLf & vbCrLf & Form_frmLogBreach.txtTitle & vbCrLf & vbCrLf & "Regards" & vbCrLf & vbCrLf & "Error Reporting Tool"
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

rec.MoveNext
Loop
rec.Close
Set rec = Nothing

Exit Sub

ErrSendBreachEmail:
MsgBox ("An error occurred whilst sending the breach notification email: " & Err.Description)
mdlDatabaseActivity.ErrorID = Err.Number
mdlDatabaseActivity.ErrorDesc = Err.Description
mdlDatabaseActivity.CentralErrorHandler "mdlDatabaseActivity", "SendBreachEmail"

Resume Next

End Sub

Can anyone spot my mistake?

Thanks in advance.

Carl

I searched the forums before posting this. My project is similar to
h t t p ://w w w .access-programmers.co.uk/forums/showthread.php?t=223148

The only problem is the file that I am saving needs to be renamed with

File Format : CurrentDate_VendorNameTitleClientWillPick.xlsx

I have this code so far. Btw I am new to this but searching online and reading forums this is what I got so far.

With this code I am able to go into Access Database. Pick out the vendor and email with attachment to all of them 1 by 1.

My next step and this is what I need help on is that Excel file that I am emailing. It has to be refreshed get all the new data from Access Database then save that as
File Format : CurrentDate_VendorNameTitleClientWillPick.xlsx

If anyone can point me in the right step I would appreciate it.


	Code:
	Option Compare Database
Option Explicit


Public Function SendEmail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim MyBodyHeader As String
Set fso = New FileSystemObject
Dim Content As DAO.Recordset
Dim Configuration As DAO.Recordset
Dim iMarker As Integer
Dim sRecipient As String
Dim aRecipients As String

' First, we need to know the subject.

' We can't  be sending around blank messages...

'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
'"We Need A Subject Line!")


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Set up the database and query connections

Set db = CurrentDb()

'This selects the table to use.

Set MailList = db.OpenRecordset("tblEmailLookup")
Set Content = db.OpenRecordset("tblEmailContent")
Set Configuration = db.OpenRecordset("Configuration")

' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.

Do Until MailList.EOF

' This creates the e-mail

Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it. Put the column name here.
MyMail.To = MailList("EmailAddress")



' Loop through for more recipients

    Do
        iMarker = InStr(1, aRecipients, ";", vbTextCompare)
        If iMarker = 0 Then
            sRecipient = aRecipients
        Else
            sRecipient = Mid(aRecipients, 1, iMarker - 1)
            aRecipients = Mid(aRecipients, iMarker + 1)
        End If
        If Len(sRecipient)  0 Then MyMail.Recipients.Add sRecipient
    Loop While iMarker  0
    

'This gives it a subject
MyMail.Subject = Content("Subject")

'This gives it the body
MyBodyHeader = "Hello " & MailList("VendorName") & "," & Chr$(13) & Chr$(13)
MyMail.Body = Content("EmailBody")

'If you want to send an attachment
'uncomment the following line


MyMail.Attachments.Add (Configuration("ExcelFilePath"))



' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"

'This sends it!

MyMail.Send
 ' MsgBox "Sending Mail To: " & MailList("VendorName")
 
'MyMail.Display



'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

' MyMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

MsgBox "Email's Sent with Attachement's"

End Function



Hi all,

I have the following code (taken from a post found in the search facility):

Dim appOutl As Object
Dim MyNameSpace As Object
Dim myemail As Object
Set appOutl = CreateObject("Outlook.Application")
Set MyNameSpace = appOutl.getNameSpace("MAPI")
Set myemail = appOutl.CreateItem(0)


myemail.to = "someone@someone.com"
myemail.Subject = "THIS IS AN AUTOMATED TEST"
myemail.body = "THIS IS THE BODY OF THE EMAIL MESSAGE"
myemail.Attachments.Add "c:something.txt", 1, 5520, "frunlog.txt"
myemail.Send

I was wondering how I attach a report in ".rtf" format to this email?

For example, a report that I would like to attach I might open from a command button with the following code:

DoCmd.OpenReport "Drivers Report", acPreview, "[ID] = " & Me.ID

I would want to attach this report (filtered with the link criteria) as an rtf attachment to my email message.

I have also noticed that outlook will not automatically send the email, it will remain in the outbox until I click send and receive within outlook. Is there any way to get around this?

Thanks for any help....

Peter

Hi,

I've used other threads here to learn how to create Outlook emails and the following is almost working. The "button" is actually an image (which I forgot to rename!) on the detail section of a continuous view form which displays relevant email addresses against data.

When I click it I get an email with the right address and subject line, exactly as I need it, but I also get another blank email. I can't see anything in my code that could be causing this.




	Code:
	Private Sub Image29_Click()

'Define variables
Dim strTo As String
Dim strSubject As String
Dim strMessage As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem

'Assign values to defined variables
strTo = Me.RDC_Email.Value

strSubject = "Quarterly upload feedback report"

strMessage = vbCrLf & vbCrLf & vbCrLf & "This is the text that will appear" & _
            vbCrLf & "in the body of the email" & vbCrLf & vbCrLf & _
            "For now I have turned this off by commenting out" & vbCrLf & "the relevant line further down."


'Create message
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


'Display the message
With objOutlookMsg
    objOutlookMsg.To = strToSQL
    objOutlookMsg.Subject = strSubject
'    objOutlookMsg.Body = strMessage
    objOutlookMsg.BodyFormat = olFormatHTML
    objOutlookMsg.Importance = olImportanceNormal
    objOutlookMsg.Display

End With
Set objOutlook = Nothing

End Sub

Any ideas what's causing this? I've closed/reopened Outlook, and compacted and repaired Access db but it's still happening.

Thanks

I've just discovered "Automation" and am trying to implement it in my Access d/b to send reports and external file attachments via e-mail. I'm not a VB programmer but I've searched on the web to find some code that mostly works OK (extract below) - I have 3 questions on this that I haven't been able to figure out:
1) Is it possible to set up ".Body" to the contents of an external file - eg: an Access report saved in RTF or HTML format, (or any other external file for that matter), rather than having to hard-code the text?
2) What changes need to be made to the code to add more than 1 attachment?
3) Is it possible to set up a link in the body of the email to open the attachments? eg: "click here to view your quotation".
Any help will be much appreciated.
--------------------------------------
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
---------------------------------------------

I use an Access database to collect data from a linked text file and other manual check boxes.

This information is displayed on a form and sub form.

I send this information out on a daily basis so have come up with an event proceedure that forms an outlook mail message. I've got the fields on the form to display correctly in the email, but the sub form is in datasheet view, getting it's data from a table in the same database.

I want the query information to display in the daily email message but am having trouble doing this through VBA code. I also don't want the query as an attachment, I want the actual query to be displayed in the mail message, gridlines and all. Anybody have suggestions?

The name of the query I want to display in the mail message is, "tundra_jobs_query"

Below is the current code that does everything but display the query results:

Private Sub Command83_Click()
'declare variables
Dim strBad As String
Dim strGood As String
Dim strGetTbl As String

'what are the variable values
strBad = "FAILED"
strGood = "OK"
strGetTbl = "SELECT * FROM tundra_jobs_query"

'create new outlook message
Set appoutlook = CreateObject("outlook.application")
Set mailoutlook = appoutlook.CreateItem(olMailItem)
With mailoutlook

'new message parameters
.To = "mail@someone.com"
.cc = "mail@someone.com"
.Subject = "Daily Production Site Report for " & Now()
'these are the checkboxes
.Body = "Data Migration ... " & IIf(Me.[Check_usr_apps_data] = True, strGood, strBad) & vbCr & _
"Search .............. " & IIf(Me.[Site_Search] = True, strGood, strBad) & vbCr & _
"Quick Order ....... " & IIf(Me.[Site_Quick_Order] = True, strGood, strBad) & vbCr & _
"Reports ............. " & IIf(Me.[Site_Report] = True, strGood, strBad) & vbCr & _
"LinkSupport ....... " & IIf(Me.[Suport_Pages] = True, strGood, strBad) & vbCr & _
"Place Order ....... " & IIf(Me.[Site_Place_Order] = True, strGood, strBad) & vbCr & _
"Email ................ " & IIf(Me.[Site_Rec_Order_Email] = True, strGood, strBad) & vbCr & _
"LNKPAS001 ...... " & IIf(Me.[LNKPAS001] = True, strGood, strBad) & vbCr & _
"LNKPAS002 ...... " & IIf(Me.[LNKPAS002] = True, strGood, strBad) & vbCr & _
"LNKPAS003 ...... " & IIf(Me.[LNKPAS003] = True, strGood, strBad) & vbCr & _
.Display
End With
End Sub

The image below is what I would like the mail message to look like. Currently I have to copy and paste the query results into the message.

Any help would be appreciated!

Here is my situation...

I have a report open on the user's window. Additionally there is a form still open underneath and to the side of the report. On that form is a button which when I click it I would like the report to be sent to an e-mail address which is entered into a field on that open form.

But I need to have a line of text entered into the subject line of the email message and also have a pretty long string of text entered into the message body of the email message.

I've tried this with a macro but evidently have too much copy in the text of the message. If I shorten it it works fine. But I really can't do that. It needs to be the entire length.

I suspect the answer to be VBA but I really don't know where to begin.

Can someone help me out with this?

--Steve

I have modified some example code from Microsoft for sending emails to each of the email addresses in a table. However I am getting. The original example was for the code to be a sub, however, I want to change it to a Function.

Where I try to run the code I get a variable not defined error at:

Set MyRS = MyDB.OpenRecordset(tblMailingList)

tblMailingList is a table with the email addresses and I know I've spelled it correctly.

If I try Set MyRS = MyDB.OpenRecordset("tblMailingList")

I get a type mismatch error

tblMailingList is spelled correctly as I pasted the name directly from the table. Also this table (for the moment) only has one field. Do I have to specify the field or something?


Thanks


The code is below:

Option Compare Database
Option Explicit


Function SendMessages()

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Dim strSubject As String
Dim strbody As String


strSubject = "HEADS UP - Periodic Samping Due Soon"
strbody = "Our records indicated that your periodic sampling and reporting of results will be due soon. Consider this a friendly reminder of that. Be aware that failure to complete the sampling and report the results is a violation subject to fine."


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(tblMailingList)
MyRS.MoveFirst


' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!ccaddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!ccaddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
'.Subject = Forms!frmMail!Subject
.Subject = strSubject
'.Body = Forms!frmMail!MainText
.Body = strbody
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
'If Not IsMissing(AttachmentPath) Then
' Set objOutlookAttach = .Attachments.Add(AttachmentPath)
'End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Function

I have code that reads Email from Outlook and searches for various pieces of text to store in a table. In addition to this, it takes the entire body of the Email, places it in a report (about 3000 or so characters) and prints the report. The code is enclosed in a for each loop to cycle through each new (unread) mail message. When the segment of code that opens and prints the report is activated, the loop will exit and unexpected results occur - sometimes another email is read and the report will print, sometimes only the first email is read and no report prints. When that section of code (to open and print the report - acViewNormal) is commented out, the loop will run fine and input all email into the table. Any ideas why opening a report would cause a loop to terminate prematurely?


I am trying to send a report via email that is in the body of the email not an attachment. In addition, I only want the current record sent, and not the entire database. Every time I run it, however, I get an error message that says "An expression you entered is the wrong data type for one of the arguments" I know I am missing something, but I cannot see it. Can someone help? Thanks. Here is my code.

Private Sub Command350_Click()
On Error GoTo Err_Command350_Click

Dim strDocName As String
Dim strWhere As String

strDocName = "IncidentReport"
strWhere = "[IntakeID]=" & Me!IntakeID
Me.Refresh
DoCmd.OutputTo acOutputReport, strDocName, acFormatHTML, "c:myreport.html", , , strWhere


Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.To = "Forms!IncidentForm!btEmail"
.Subject = "Incident Report"
.BodyFormat = olFormatHTML
.HTMLBody = "C:myreport.html"
.Display
End With



Exit_Command350_Click:
Exit Sub

Err_Command350_Click:
MsgBox Err.Description
Resume Exit_Command350_Click

End Sub

Hi

I have hijacked a bit of code to facilitate grabbing a whole lot of email address from a table and put them into an email. I have modified the code so rather than create a seperate one for each email address they all go into one email. The problem that I have is that they all go into the To: field and I need them to be in the BCC: field in the email. I have looked on this site and many others and have not found a solution. If I post the code below, I would really appreciate the help to get this working.

Also, when then the email opens up in Outlook, for some reason it does not have my signature which usually comes up when I open a new email. Is there something I can do to make this happen?

I am using Office 2003.

Thanks for your assistance.

Callum


Public Function SendEmail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String



Set fso = New FileSystemObject
' First, we need to know the subject.
' We canít very well be sending around blank messages...
'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
'"We Need A Subject Line!")
' If thereís no subject, call it a day.
'If Subjectline$ = "" Then
'MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "E-Mail Merger"
'Exit Function
'End If
' Now we need to put something in our letter...
'
'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
'"We Need A Body!")
' If thereís nothing to say, call it a day.
'If BodyFile$ = "" Then
'MsgBox "No body, no message." & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "I Ainít Got No-Body!"
'Exit Function
'End If

' Check to make sure the file exists...
'If fso.FileExists(BodyFile$) = False Then
'MsgBox "The body file isnít where you say it is. " & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "I Ainít Got No-Body!"
'Exit Function
'
'End If
' Since we got a file, we can open it up.
'Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
' and read it into a variable.
'MyBodyText = MyBody.ReadAll
' and close the file.
'MyBody.Close


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections

Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")


' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' adding them to e-mails and sending them.
Do Until MailList.EOF

' This addresses it
MyMail.Recipients.Add MailList("email")














' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
MailList.MoveNext
Loop





'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
MyMail.Body = MyBodyText
'If you want to send an attachment
'uncomment the following line
'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"
' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you donít want the attachmentís icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"
'This sends it!
'MyMail.Send
'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.
MyMail.Display
'And on to the next one...



'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function


Not finding an answer? Try a Google search.