473,890 Members | 2,051 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 7942
NeoPa
32,584 Recognized Expert Moderator MVP
As I suspected then. Nothing to do with VBA (VBA is the only place you'd expect VBA constants to be recognised in).

As I said before, I'm not in any position to help you further with your macro work I'm afraid. My best advice would be to avoid macros in all situations. They are clumsy and inflexible and they give you nothing that VBA can't do better in various ways.
Aug 2 '11 #11
Jeffrey Tan
86 New Member
yeah it's kinda limited in terms of what i can do with it.

the only solution i can think of is to somehow right click on the report print preview > send to > mail recipient > and it'll attach as an attachment. other than that, i will also have to look at the References for outlook and see what i can come up with.
Aug 2 '11 #12
NeoPa
32,584 Recognized Expert Moderator MVP
I haven't got much, but this link (Problem with e-mailing from within Access) may get you started at least.

Maybe it's time I started getting into this problem (of emailing from Access). I know it comes up a lot (regularly if not frequently) so I'll see if I can make some time to get better acquainted.
Aug 2 '11 #13
ADezii
8,834 Recognized Expert Expert
@Jeffrey Tan:
I use similar type functionality all the time, so I created some Code for you that you may find slightly useful. It is relatively simple and uses Automation Code to send an E-Mail and Attachment (Report as HTML) from Access via Outlook. The Code will:
  1. Avoid the Outlook Warning Message.
  2. Automatically fill in the Recipient, Body, and Subject Fields in the Outlook Window.
  3. Saves a specified Report as HTML and subsequently attaches it to your E-Mail.
  4. 'Displays' the Outlook Window as opposed to automatically sending the E-Mail.
  5. May provide a substitute for vbNewLine in order to avoid the problems that you are currently experiencing.
  6. Simply set a Reference to the Microsoft Outlook XX.X Object Library, substitute your own Values in the Variable Block, and then fire away.
  7. Any further questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  2. Dim oLook As Object
  3. Dim oMail As Object
  4. Dim olns As Outlook.NameSpace
  5. Dim strRecipient As String
  6. Dim strBody As String
  7. Dim strSubject As String
  8. Dim strReportName As String
  9.  
  10. Set oLook = CreateObject("Outlook.Application")
  11. Set olns = oLook.GetNamespace("MAPI")
  12. Set oMail = oLook.CreateItem(0)
  13.  
  14. '************************** Define your own Values here **************************
  15. strRecipient = "JTan@aol.com"
  16. strBody = "To be or not to be, " & Chr$(13) & Chr$(10) & "that is the question"
  17. strSubject = "Demo Database for Jeffrey Tan"
  18. strReportName = "rptEmployees"
  19. '*********************************************************************************
  20.  
  21. DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, CurrentProject.Path & _
  22.                "\" & strReportName & ".htm", False
  23.  
  24. With oMail
  25.   .To = strRecipient
  26.   .Body = strBody
  27.   .Subject = strSubject
  28.   .Attachments.Add CurrentProject.Path & "\" & strReportName & ".htm", False
  29.     .Display
  30. End With
  31.  
  32. Set oMail = Nothing
  33. Set oLook = Nothing
Aug 2 '11 #14
Jeffrey Tan
86 New Member
@ADezii:

That looks really self explanatory. Thanks! I'll give it a try and report back if I have any problems.
Aug 2 '11 #15
Jeffrey Tan
86 New Member
@ADezii:

Yep still experiencing that report problem.

It's attaching all the records instead of the specific record i'm on.

I can't seem to figure what I did wrong.

This is my query that the report is based on

Expand|Select|Wrap|Line Numbers
  1. SELECT user_problem_log.*, usr_problem_list.trouble_no, usr_problem_list.date, 
  2. usr_problem_list.user, usr_problem_list.notes, users.First_Name, users.Last_Name, 
  3. users.extension, offices.Office_Name, users.group_name, users.email
  4. FROM user_problem_log 
  5. INNER JOIN ((offices INNER JOIN users 
  6. ON offices.office_number = users.Office) 
  7. INNER JOIN usr_problem_list ON users.win_id = usr_problem_list.user) ON user_problem_log.trouble_no = usr_problem_list.trouble_no;
  8.  
I designed the report the way I normally do:
- label in report header
- rest of the fields in Detail
Aug 2 '11 #16
Jeffrey Tan
86 New Member
Anyway to incorporate this in the code?

Expand|Select|Wrap|Line Numbers
  1. [user_problem_log].[trouble_no]=[Forms]![User Problem Log]![Text30]
Text30 is the autonumber field.

The above code is taken form the print macro

Report Name: Sub Tickets-Print via Form
View: Print Preview
Filter Name:
Where Condition: see above
Window Mode: Normal

I believe the reason why the code to attach the report attached everything is because access doesn't know which report it is so it grabs every record?
Aug 2 '11 #17
ADezii
8,834 Recognized Expert Expert
@Jeffrey Tan:
As previously indicated by NeoPa, this seems to be a purely SQL issue as opposed to VBA. The addition of a WHERE Clause in the SQL Statement may do the trick.
Aug 2 '11 #18
Jeffrey Tan
86 New Member
@ADezii

Yeah I don't know where to include that part and somehow tie it along with the report name in the strReportName. This is the part I'm really confused on. :(
Aug 2 '11 #19
Jeffrey Tan
86 New Member
OMG LOL I think I got it.

I just added that where clause in the query that the report uses!

I'll try that again and see if it really does work.

The last question I have...

when the attachment of the report is attached, it takes the name of the report name... so in my case it attaches it as

"Sub Email Single Ticket.htm (5kb)"

Can we somehow change the name of that to something else? Thanks a bunch! Really appreciate the help!!

-edit-
nvm got it :D
Aug 2 '11 #20

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

Similar topics

1
1922
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
4107
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
2882
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
9970
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
9810
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,...
0
11207
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10896
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,...
1
8000
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
7153
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6031
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4652
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
3
3259
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.