473,882 Members | 1,576 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

send email without using a macro

86 New Member
Hi. I was wondering if there was a way to send an email without using the Send Object method for a macro. if you use the macro, you will get this warning:



I know in VB .NET you have to import an outlook object or w/e it is and then there were methods that allowed you to edit the To, CC, BCC, subject, etc

I was wondering if there is something similar in VBA to do that.

What i want to do is the following:
1) when user clicks "Send Email" button, it opens up MS Outlook
2) it attaches a report
3) fills out the subject

Thanks!
Aug 1 '11
68 7940
ADezii
8,834 Recognized Expert Expert
I just added that where clause in the query that the report uses!
Correctamundo, that's what I was referring to.
Can we somehow change the name of that to something else? Thanks a bunch!
The following Code will provide the Attached Report with a Unique Name based on a Base Name, the Current Date, and the Current Time. Pay special attention to Code Line Numbers: 10, 21, 25 and 31:
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail and Attachment from
  2. 'Access via Outlook
  3. Dim oLook As Object
  4. Dim oMail As Object
  5. Dim olns As Outlook.NameSpace
  6. Dim strRecipient As String
  7. Dim strBody As String
  8. Dim strSubject As String
  9. Dim strReportName As String
  10. Dim strRptAttachName As String
  11.  
  12. Set oLook = CreateObject("Outlook.Application")
  13. Set olns = oLook.GetNamespace("MAPI")
  14. Set oMail = oLook.CreateItem(0)
  15.  
  16. '************************** Define your own Values here **************************
  17. strRecipient = "ADezii@aol.com"
  18. strBody = "To be or not to be, " & Chr$(13) & Chr$(10) & "that is the question"
  19. strSubject = "Demo Database for Jeffrey Tan"
  20. strReportName = "rptEmployees"
  21. strRptAttachName = "JTs_Report_" & Format$(Now, "mmddyyyy_hhmmss")
  22. '*********************************************************************************
  23.  
  24. DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, CurrentProject.Path & _
  25.                "\" & strRptAttachName & ".htm", False
  26.  
  27. With oMail
  28.   .To = strRecipient
  29.   .Body = strBody
  30.   .Subject = strSubject
  31.   .Attachments.Add CurrentProject.Path & "\" & strRptAttachName & ".htm", False
  32.     .Display
  33. End With
  34.  
  35. Set oMail = Nothing
  36. Set oLook = Nothing
BTW, how did the vbNewLine substitution work out? You may also want to incorporate Code into the Logic that will DELETE the Report as formatted in HTML after Processing/Attaching it.
Aug 2 '11 #21
Jeffrey Tan
86 New Member
@ADezii

I just used the following for the report name attachment
Expand|Select|Wrap|Line Numbers
  1. strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  2.  
  3. DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  4.                "\" & strReportName & ".htm", False
  5.  
learned it from another question i asked before :D

And yes the Chr$10 and 13 worked out perfectly! Very nice :D Will keep that one in mind.

Yeah I will have to add code after it attachs the report to delete the HTML file. Is it pretty easy to do that?

-edit-
or how about when it attaches the report, move it to the user's temp folder and let windows deal with it :)
Aug 2 '11 #22
ADezii
8,834 Recognized Expert Expert
Yeah I will have to add code after it attachs the report to delete the HTML file. Is it pretty easy to do that?
  1. Be careful not to DELETE the File until after it has been Sent.
  2. You can DELETE ALL HTML (*.htm) Files in the Currentproject. Path at any time, via:
    Expand|Select|Wrap|Line Numbers
    1. 'DELETE ALL HTML (*.htm) Files in the CurrentProject.Path Directory
    2. If Dir$(CurrentProject.Path & "\*.htm") <> "" Then Kill CurrentProject.Path & "\*.htm"
Aug 2 '11 #23
Jeffrey Tan
86 New Member
@ADezii

what do you mean by #1. Isn't the html file we created the only to be deleted?

I tried to use the following to move the file to another folder, but I get an error saying the file already exists

Expand|Select|Wrap|Line Numbers
  1. Name CurrentProject.Path & "\" & strReportName & ".htm" As "C:\Documents and Settings\jtan\Desktop\folder"
-edit-

silly me. left out the file name at the end of the new directory. should be

Expand|Select|Wrap|Line Numbers
  1. Name CurrentProject.Path & "\" & strReportName & ".htm" As "C:\Documents and Settings\jtan\Desktop\folder" & strReportName & ".htm"
Aug 2 '11 #24
ADezii
8,834 Recognized Expert Expert
Expand|Select|Wrap|Line Numbers
  1. Dim strSource As String
  2. Dim strDestination As String
  3.  
  4. strSource = CurrentProject.Path & "\"
  5. strDestination = "C:\Documents and Settings\jtan\Desktop\"
  6.  
  7. Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm"
Aug 2 '11 #25
Jeffrey Tan
86 New Member
@ADzeii

Thanks! I guess this problem is now solved :D

You guys are great!!
Aug 2 '11 #26
Jeffrey Tan
86 New Member
Hmm another problem now :P

Now what happen's if there are two or more pages of a report? :D

-edit-
the ones with two pages of a report are named with Page2.htm, Page3.htm etc with the report name preceding...
Aug 2 '11 #27
Jeffrey Tan
86 New Member
now converting them to a PDF would solve the problem, but I'm not sure if there's such thing as acFormatPDF
Aug 2 '11 #28
ADezii
8,834 Recognized Expert Expert
but I'm not sure if there's such thing as acFormatPDF
I think that Access 2007 has this option, but if not there are always the *.rtf and *.snp Formats. To me, the *.snp (Snapshot) Format is very impressive. I'm not positive, but I think that the Snapshot Viewer will be installed on Client PCs on first time usage. Rich Text Format is pretty much universal, and usually displays quite well from what I have seen.
Aug 2 '11 #29
Jeffrey Tan
86 New Member
@ADezii,

I tried rtf but i dont think it works.

As for SNP, it does work (puts multiple reports in one .snp file), however, ms outlook security disables viewing any .snp file :(

I was thinking to set a count of how many files there are for one specific record and add it to a counter. say if there were 4 pages of record #1, then four would be added and then it would do the
.Attachements.A dd ..... Page1.htm until it goes to the last page.

-edit-

hrm.. RTF does work.. but i think in the long run PDF will be best. i did a bit of research and to get ACFORMATPDF to work for 2003, thre is a lot of work to be done :(
Aug 2 '11 #30

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1921
by: dave | last post by:
hi guys I m trying to execute few lines code tht i have copies from microsoft tech script centre. basically its to send email without using any smtp service. u can find the code snippet from following link. http://www.microsoft.com/technet/community/scriptcenter/en tscr/scrent16.mspx while executing this code in vbs file i was getting error:
3
4354
by: jdph40 | last post by:
I'm using Access 97. In the OnClick event of a button I have the code below. I looks at txtEmail and sends an e-mail message without further action from the user (user does not have to click Send as he would if he used DoCmd.SendObject.) However, I want to attach a report in Snapshot format. How would I modify my code to include a report? Thank you, JD
1
4106
by: Ronny Sigo | last post by:
Hello all, Can anyone tell me how to prevent my mailprog to display the mail, and just send it ? The code I use is below Any help appreciated .. Thanks Ronny Sigo Private Declare Function ShellExecute Lib "shell32.dll" Alias _ "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _
6
11006
by: Mike | last post by:
In VB6 I could send email via Outlook without using SMTP, Is it possible to do the same in .NET and if so is there any examples of doing this?
4
2264
by: CLEAR-RCIC | last post by:
I want to send an email from our Intranet site to myself whenever an error happens. Our manager will not let us install SMTP on the web server. Is it possible to send email programatically without SMTP installed?
6
17113
by: ErwinF | last post by:
Hi there, I would like to know the following: How to send send email attachments using WebDAV in VB .NET? Sample code please................... Thanks for your help.
3
2881
by: Siv | last post by:
Hi, A little while ago I wrote a small program that allowed the user to view products from a database. The database holds the details of the products which can be viewed via a form and additionally pictures of the product are stored in an images subfolder and the database holds the file name of the relevant picture. The user can then click a button to display the picture in a pop-up window and also another button to email the potential...
2
3356
by: Boki | last post by:
Hi All, I want to create a program that auto send out email ( not email bomb ) to myself. I tried the MAPI, but it will show a notiifcation message, user need to confirm that. My target is the program can auto send out email for a while ( with a timer, no prob )
3
6647
by: phill86 | last post by:
Hi I am using the following code to send an email from access via outlook automatically DoCmd.SendObject acSendReport, stDocName, acFormatRTF, "User", , , , , False
0
9931
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10830
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10403
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9557
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7956
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5978
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4601
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.