473,854 Members | 1,830 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 7938
Jeffrey Tan
86 New Member
How do I determine the number of files in a directory that have certain words in the file name?

I have this function here
Expand|Select|Wrap|Line Numbers
  1. Function countfiles(folder As String)
  2.  
  3. Dim directory As String, countOf As Long
  4. directory = Dir$(folder & "\*.htm")
  5. Do Until directory = ""
  6.     countOf = (countOf + 1)
  7.     directory = Dir$()
  8. Loop
  9.  
  10. MsgBox countOf
  11.  
  12. End Function
i want to see the number of htm files that have the words "Single Problem Tracking Ticket #9.htm"

some may have "Single Problem Tracking Ticket #9Page2.htm" Page3, etc
Aug 3 '11 #31
ADezii
8,834 Recognized Expert Expert
Pass another Argument to the Function that will be the String to search for in the Filename:
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Function countfiles(folder As String, strStringToSearch As String)
    2. Dim directory As String
    3. Dim countOf As Long
    4.  
    5. directory = Dir$(folder & "\*.htm")
    6.  
    7. Do Until directory = ""
    8.   If InStr(directory, strStringToSearch) > 0 Then
    9.     countOf = (countOf + 1)
    10.   End If
    11.   directory = Dir$()
    12. Loop
    13.  
    14. MsgBox countOf & " File(s) in " & folder & " contain(s) the String [" & strStringToSearch & "]"
    15. End Function
  2. Sample Call:
    Expand|Select|Wrap|Line Numbers
    1. countfiles("<Folder to Search>","Single Problem Tracking Ticket #9.htm")
Aug 3 '11 #32
NeoPa
32,584 Recognized Expert Moderator MVP
ADezii:
Be careful not to DELETE the File until after it has been Sent.
Generally, a copy of the attachment is stored within your email database (wherever that is on your system) once it's been attached, so that should make it safe to delete once attached. My experience is mainly with Outlook though, so do test this before relying on it 100%
ADezii:
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"
Just a point to mention that web pages often have associated folders to store any images etc that relate to the page. I don't currently have code that does recursive folder tree deletion, but I may work on one when I get a chance.
Aug 3 '11 #33
NeoPa
32,584 Recognized Expert Moderator MVP
ADezii:
Expand|Select|Wrap|Line Numbers
  1. directory = Dir$(folder & "\*.htm")
You may simply want to say (in line #5) :
Expand|Select|Wrap|Line Numbers
  1. directory = Dir$(folder & "\" & strStringToSearch & "*.htm")
Aug 3 '11 #34
ADezii
8,834 Recognized Expert Expert
@NeoPa:
I was thinking more along the lines of Attaching the File, Deleting the File itself, then Canceling the E-Mail. You can now no longer go back to Re-attach the same File since it will no longer exist. Make sense to you, or am I on another one of my Tangents? (LOL).
Aug 3 '11 #35
Jeffrey Tan
86 New Member
@ADezii:

I will use your code instead for the search string :D

very nice.

As for the acFormatPDF, i gave up on PDF :P will just attach it as X amount of HTM files if report has X amount of pages.

Somehow, somewhere, MS Outlook is referencing the attachement as a shortcut? And when I send the email (to myself to test), MS outlook blocked it....

The code from yesterday (sending only one file) worked...

I don't know what I did wrong that made MS Outlook get a shortcut of the file.

I'll paste the code I worked on here in the next post.
Aug 3 '11 #36
Jeffrey Tan
86 New Member
The first code is a function to see if a file exists or not
Expand|Select|Wrap|Line Numbers
  1. Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
  2.  
  3.     Dim lngAttributes As Long
  4.  
  5.     'Include read-only files, hidden files, system files.
  6.     lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
  7.  
  8.     If bFindFolders Then
  9.         lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
  10.     Else
  11.         'Strip any trailing slash, so Dir does not look inside the folder.
  12.         Do While Right$(strFile, 1) = "\"
  13.             strFile = Left$(strFile, Len(strFile) - 1)
  14.         Loop
  15.     End If
  16.  
  17.     'If Dir() returns something, the file exists.
  18.     On Error Resume Next
  19.     FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
  20. End Function
  21.  
The next function is to count the files containing the report name excluding the Page2, Page3, etc
Expand|Select|Wrap|Line Numbers
  1. Function countfiles(folder As String)
  2.  
  3. Dim directory As String, countOf As Long
  4.  
  5. directory = Dir$(folder & "\Single Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no] & "*", vbNormal)
  6. Do Until directory = ""
  7.     countOf = (countOf + 1)
  8.     directory = Dir$
  9. Loop
  10.  
  11. countfiles = countOf
  12.  
  13. 'directory = Dir$(folder & "\Single Problem Tracking Ticket # 9*")
  14. End Function
  15.  
This is the code that sets up the email part
Expand|Select|Wrap|Line Numbers
  1. Sub sendEmail()
  2.  
  3. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  4. Dim oLook As Object
  5. Dim oMail As Object
  6. Dim olns As Outlook.NameSpace
  7. Dim strRecipient As String
  8. Dim strBody As String
  9. Dim strSubject As String
  10. Dim strReportName As String
  11. Dim strSource As String
  12. Dim strDestination As String
  13. Dim numofFiles As Integer
  14. Dim attachfile As String
  15.  
  16. Set oLook = CreateObject("Outlook.Application")
  17. Set olns = oLook.GetNamespace("MAPI")
  18. Set oMail = oLook.CreateItem(0)
  19.  
  20. '************************** Define your own Values here **************************
  21. strRecipient = ""
  22. strBody = ""
  23. strSubject = "Problem Tracking Ticket Number: " & [Forms]![User Problem Log]![trouble_no]
  24. strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  25.  
  26. '*********************************************************************************
  27.  
  28. 'DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  29. '               "\" & strReportName & ".htm", False
  30.  
  31. strSource = CurrentProject.Path & "\"
  32. strDestination = "C:\Windows\Temp\"
  33.  
  34. If Len(Dir(strDestination, vbDirectory)) = 0 Then 'if directory does not exist
  35.  
  36.     MkDir (strDestination) 'create it
  37.  
  38. End If
  39.  
  40. If FileExists(strDestination & strReportName & "*") = False Then 'if file does not exist
  41.  
  42.     DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  43.                "\" & strReportName & ".htm", False 'create html file page1, page2, etc
  44.  
  45.     'numofFiles = countfiles("C:\Windows\Temp")
  46.  
  47.     numofFiles = countfiles("C:\Documents and Settings\jtan\Desktop\backup")
  48.  
  49.     If numofFiles > 1 Then
  50.         Dim i As Integer
  51.         For i = 2 To numofFiles
  52.             Name CurrentProject.Path & "\" & strReportName & "Page" & i & ".htm" As strDestination & strReportName & "Page" & i & ".htm" 'move to C:\Windows\Temp 
  53.         Next
  54.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  55.         Else
  56.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  57.     End If       
  58. Else 'if file does exist
  59.     Kill strDestination & strReportName & "*" 'delete it
  60.     DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  61.                "\" & strReportName & ".htm", False 'recreate it
  62.  
  63.     numofFiles = countfiles("C:\Documents and Settings\jtan\Desktop\backup")
  64.  
  65.     If numofFiles > 1 Then
  66.         For i = 2 To numofFiles
  67.             Name CurrentProject.Path & "\" & strReportName & "Page" & i & ".htm" As strDestination & strReportName & "Page" & i & ".htm" 'move to C:\Windows\Temp
  68.         Next
  69.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  70.         Else
  71.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  72.  
  73.     End If
  74.  
  75. End If
  76.  
  77. oMail.To = strRecipient
  78. oMail.Body = strBody
  79. oMail.Subject = strSubject
  80.  
  81. If numofFiles > 1 Then
  82.     oMail.Attachments.Add strDestination & "\" & strReportName & ".htm", False
  83.     For i = 2 To numofFiles
  84.         oMail.Attachments.Add strDestination & "\" & strReportName & "Page" & i & ".htm", False
  85.     Next
  86. Else
  87.     oMail.Attachments.Add strDestination & "\" & strReportName & ".htm", False
  88.  
  89. End If
  90.  
  91. oMail.Display
  92.  
  93. 'With oMail
  94. '  .To = strRecipient
  95. '  .Body = strBody
  96. '  .Subject = strSubject
  97. '  .Attachments.Add strDestination & "\" & strReportName & ".htm", False
  98. '  .Display
  99. 'End With
  100.  
  101. Set oMail = Nothing
  102. Set oLook = Nothing
  103.  
  104. End Sub
sorry for the sloppiness
Aug 3 '11 #37
Jeffrey Tan
86 New Member
Is .Attachments add attaching a shortcut or the file directly?

^ problem with MS outlook restricting the ones that are shortcuts.
Aug 3 '11 #38
Jeffrey Tan
86 New Member
nvm about the previous code.

this is the updated one

Expand|Select|Wrap|Line Numbers
  1. Sub sendEmail()
  2.  
  3. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  4. Dim oLook As Object
  5. Dim oMail As Object
  6. Dim olns As Outlook.NameSpace
  7. Dim strRecipient As String
  8. Dim strBody As String
  9. Dim strSubject As String
  10. Dim strReportName As String
  11. Dim strSource As String
  12. Dim strDestination As String
  13. Dim numofFiles As Integer
  14. Dim title As String
  15.  
  16. Set oLook = CreateObject("Outlook.Application")
  17. Set olns = oLook.GetNamespace("MAPI")
  18. Set oMail = oLook.CreateItem(0)
  19.  
  20. '************************** Define your own Values here **************************
  21. strRecipient = ""
  22. strBody = ""
  23. strSubject = "Problem Tracking Ticket #: " & [Forms]![User Problem Log]![trouble_no]
  24. strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  25.  
  26. '*********************************************************************************
  27.  
  28. strSource = CurrentProject.Path & "\"
  29. strDestination = "C:\Documents and Settings\jtan\Desktop\Files\"
  30. title = "Single Problem Tracking Ticket # "
  31.  
  32.     If Not FileExists(strDestination) Then
  33.         MkDir (strDestination)
  34.     End If
  35.  
  36.     If FileExists(strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*") Then
  37.         'deletes all files related to that trouble #
  38.         Kill strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*"
  39.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, strDestination & _
  40.                "\" & strReportName & ".htm", False 'create html file page1, page2, etc
  41.     Else
  42.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, strDestination & _
  43.                "\" & strReportName & ".htm", False 'create html file page1, page2, etc
  44.     End If
  45.  
  46.     With oMail
  47.       .To = strRecipient
  48.       .Body = strBody
  49.       .Subject = strSubject
  50.  
  51.       numofFiles = countfiles(strDestination)
  52.  
  53.     If numofFiles > 1 Then
  54.         'adds first page (i.e. Single Problem Tracking Ticket # 9.htm"
  55.         .Attachments.Add strDestination & "\" & strReportName & ".htm", False
  56.         'if more than 2 htm files
  57.         Dim i As Integer
  58.         For i = 2 To numofFiles
  59.             'adds page 2 and on (i.e. Single Problem Tracking Ticket # 9Page2.htm, Page3.htm, etc)
  60.             .Attachments.Add strDestination & "\" & strReportName & "Page" & i & ".htm", False
  61.         Next
  62.     Else
  63.         .Attachments.Add strDestination & "\" & strReportName & ".htm", False
  64.     End If
  65.       .Display 'display ms outlook
  66.     End With
however, MS outlook is still saying it's a shortcut and blocks it. :(
Aug 3 '11 #39
Jeffrey Tan
86 New Member
and problem is solved. :)

supervisor decided to migrate over to 2007 so PDF is the way to go since there's an acFormatPDF :).
Aug 3 '11 #40

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
4105
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
11005
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
2878
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
6646
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
9903
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
11041
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...
0
10373
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
9525
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
7921
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
5753
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5946
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4565
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
4167
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.