send email without using a macro

[Jeffrey Tan]

How do I determine the number of files in a directory that have certain words in the file name?

I have this function here

Function countfiles(folder As String)

Dim directory As String, countOf As Long
directory = Dir$(folder & "\*.htm")
Do Until directory = ""
    countOf = (countOf + 1)
    directory = Dir$()
Loop

MsgBox countOf

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

[ADezii]

Pass another Argument to the Function that will be the String to search for in the Filename:

1. Function Definition:
   

   Function countfiles(folder As String, strStringToSearch As String)
   Dim directory As String
   Dim countOf As Long
   
   directory = Dir$(folder & "\*.htm")
   
   Do Until directory = ""
     If InStr(directory, strStringToSearch) > 0 Then
       countOf = (countOf + 1)
     End If
     directory = Dir$()
   Loop
     
   MsgBox countOf & " File(s) in " & folder & " contain(s) the String [" & strStringToSearch & "]"
   End Function

2. Sample Call:
   

   countfiles("<Folder to Search>","Single Problem Tracking Ticket #9.htm")

[NeoPa]

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:

'DELETE ALL HTML (*.htm) Files in the CurrentProject.Path Directory
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.

[NeoPa]

ADezii:

directory = Dir$(folder & "\*.htm")

You may simply want to say (in line #5) :

directory = Dir$(folder & "\" & strStringToSearch & "*.htm")

[ADezii]

@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).

[Jeffrey Tan]

@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.

[Jeffrey Tan]

The first code is a function to see if a file exists or not

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean

    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

The next function is to count the files containing the report name excluding the Page2, Page3, etc

Function countfiles(folder As String)

Dim directory As String, countOf As Long

directory = Dir$(folder & "\Single Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no] & "*", vbNormal)
Do Until directory = ""
    countOf = (countOf + 1)
    directory = Dir$
Loop

countfiles = countOf

'directory = Dir$(folder & "\Single Problem Tracking Ticket # 9*")
End Function

This is the code that sets up the email part

Sub sendEmail()

'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.NameSpace
Dim strRecipient As String
Dim strBody As String
Dim strSubject As String
Dim strReportName As String
Dim strSource As String
Dim strDestination As String
Dim numofFiles As Integer
Dim attachfile As String
  
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
  
'************************** Define your own Values here **************************
strRecipient = ""
strBody = ""
strSubject = "Problem Tracking Ticket Number: " & [Forms]![User Problem Log]![trouble_no]
strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]

'*********************************************************************************
  
'DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
'               "\" & strReportName & ".htm", False

strSource = CurrentProject.Path & "\"
strDestination = "C:\Windows\Temp\"

If Len(Dir(strDestination, vbDirectory)) = 0 Then 'if directory does not exist
    
    MkDir (strDestination) 'create it
    
End If

If FileExists(strDestination & strReportName & "*") = False Then 'if file does not exist
    
    DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
               "\" & strReportName & ".htm", False 'create html file page1, page2, etc
               
    'numofFiles = countfiles("C:\Windows\Temp")
    
    numofFiles = countfiles("C:\Documents and Settings\jtan\Desktop\backup")
    
    If numofFiles > 1 Then
        Dim i As Integer
        For i = 2 To numofFiles
            Name CurrentProject.Path & "\" & strReportName & "Page" & i & ".htm" As strDestination & strReportName & "Page" & i & ".htm" 'move to C:\Windows\Temp 
        Next
            Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
        Else
            Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
    End If       
Else 'if file does exist
    Kill strDestination & strReportName & "*" 'delete it
    DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
               "\" & strReportName & ".htm", False 'recreate it
    
    numofFiles = countfiles("C:\Documents and Settings\jtan\Desktop\backup")
    
    If numofFiles > 1 Then
        For i = 2 To numofFiles
            Name CurrentProject.Path & "\" & strReportName & "Page" & i & ".htm" As strDestination & strReportName & "Page" & i & ".htm" 'move to C:\Windows\Temp
        Next
            Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
        Else
            Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
    
    End If
    
End If

oMail.To = strRecipient
oMail.Body = strBody
oMail.Subject = strSubject

If numofFiles > 1 Then
    oMail.Attachments.Add strDestination & "\" & strReportName & ".htm", False
    For i = 2 To numofFiles
        oMail.Attachments.Add strDestination & "\" & strReportName & "Page" & i & ".htm", False
    Next
Else
    oMail.Attachments.Add strDestination & "\" & strReportName & ".htm", False

End If

oMail.Display

'With oMail
'  .To = strRecipient
'  .Body = strBody
'  .Subject = strSubject
'  .Attachments.Add strDestination & "\" & strReportName & ".htm", False
'  .Display
'End With

Set oMail = Nothing
Set oLook = Nothing

End Sub

sorry for the sloppiness

[Jeffrey Tan]

Is .Attachments add attaching a shortcut or the file directly?

^ problem with MS outlook restricting the ones that are shortcuts.

[Jeffrey Tan]

nvm about the previous code.

this is the updated one

Sub sendEmail()

'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.NameSpace
Dim strRecipient As String
Dim strBody As String
Dim strSubject As String
Dim strReportName As String
Dim strSource As String
Dim strDestination As String
Dim numofFiles As Integer
Dim title As String
  
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
  
'************************** Define your own Values here **************************
strRecipient = ""
strBody = ""
strSubject = "Problem Tracking Ticket #: " & [Forms]![User Problem Log]![trouble_no]
strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]

'*********************************************************************************
  
strSource = CurrentProject.Path & "\"
strDestination = "C:\Documents and Settings\jtan\Desktop\Files\"
title = "Single Problem Tracking Ticket # "

    If Not FileExists(strDestination) Then
        MkDir (strDestination)
    End If

    If FileExists(strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*") Then
        'deletes all files related to that trouble #
        Kill strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*"
        DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, strDestination & _
               "\" & strReportName & ".htm", False 'create html file page1, page2, etc
    Else
        DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, strDestination & _
               "\" & strReportName & ".htm", False 'create html file page1, page2, etc
    End If

    With oMail
      .To = strRecipient
      .Body = strBody
      .Subject = strSubject
      
      numofFiles = countfiles(strDestination)
      
    If numofFiles > 1 Then
        'adds first page (i.e. Single Problem Tracking Ticket # 9.htm"
        .Attachments.Add strDestination & "\" & strReportName & ".htm", False
        'if more than 2 htm files
        Dim i As Integer
        For i = 2 To numofFiles
            'adds page 2 and on (i.e. Single Problem Tracking Ticket # 9Page2.htm, Page3.htm, etc)
            .Attachments.Add strDestination & "\" & strReportName & "Page" & i & ".htm", False
        Next
    Else
        .Attachments.Add strDestination & "\" & strReportName & ".htm", False
    End If
      .Display 'display ms outlook
    End With

however, MS outlook is still saying it's a shortcut and blocks it. :(

[Jeffrey Tan]

and problem is solved. :)

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

[NeoPa]

I would say one of the very few real benefits introduced in 2007 was the acFormatPDF option.

[Jeffrey Tan]

@NeoPa: I definitely agree...

I don't like the tabs on top and it makes finding things a lot harder...

if MS kept with one UI and just changed some features, everyone would be happy :D

Now I can use this docmd.outputto for my other reports and attach em as PDFs :D.

Thanks everyone who helped me once again!

[NeoPa]

Jeffrey:
if MS kept with one UI and just changed some features, everyone would be happy :D

So happy in fact, that far fewer would be bothering to keep up with buying all the new releases ;-)

[Jeffrey Tan]

@NeoPa or @ADezii

i am having problems sending the email from my windows xp laptop. i installed office 2007 and converted the database to 2007. my colleague tried it on his windows 7 desktop and was able to send the email.

When i open access 2007 and open up the form and click on the email button, i get the following error:

Run-time erro '2282':
The format in which you are attempting to output the current object is not available.

anyone know what this means?

Thanks!

[ADezii]

1. Make sure that your Output Format is set to:
   

   acFormatPDF

2. Referring to your prior attempts in Attaching the *.htm Files, try changing all occurrances of .htm in the Code to .html.