Need help in understanding Email Code

[recyan]

Came across the below code & was trying to understand it ( the portion marked in red).


http://www.devhut.net/2010/09/03/vba...ok-automation/

Need help in understanding the red marked portion.

Code posted for reference :

' Procedure : SendHTMLEmail
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Automate Outlook to send an HTML email with or without attachments
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTo         To Recipient email address string (semi-colon separated list)
' strSubject    Text string (HTML) to be used as the email subject line
' strBody       Text string to be used as the email body (actual message)
' bEdit         True/False whether or not you wish to preview the email before sending
' strBCC        BCC Recipient email address string (semi-colon separated list)
' AttachmentPath    single value or array of attachment (complete file paths with
'                   filename and extensions)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-Nov-16             Initial Release
'---------------------------------------------------------------------------------------
Function SendHTMLEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _
                   Optional strBCC As Variant, Optional AttachmentPath As Variant)
'Send Email using late binding to avoid reference issues
   Dim objOutlook As Object
   Dim objOutlookMsg As Object
   Dim objOutlookRecip As Object
   Dim objOutlookAttach As Object
   Dim i As Integer
   Const olMailItem = 0

   On Error GoTo ErrorMsgs

   Set objOutlook = CreateObject("Outlook.Application")

   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
      Set objOutlookRecip = .Recipients.Add(strTo)
      objOutlookRecip.Type = 1

      If Not IsMissing(strBCC) Then
        Set objOutlookRecip = .Recipients.Add(strBCC)
        objOutlookRecip.Type = 3
      End If

      .Subject = strSubject
      .HTMLBody = strBody
      .Importance = 2  'Importance Level  0=Low,1=Normal,2=High

      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
        If IsArray(AttachmentPath) Then
           For i = LBound(AttachmentPath) To UBound(AttachmentPath)
              If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))
              End If
           Next i
        Else
            If AttachmentPath <> "" And AttachmentPath(i) <> "False" Then
                Set objOutlookAttach = .Attachments.Add(AttachmentPath)
            End If
        End If
      End If

      For Each objOutlookRecip In .Recipients
         If Not objOutlookRecip.Resolve Then
            objOutlookMsg.Display
         End If
      Next

      If bEdit Then 'Choose btw transparent/silent send and preview send
        .Display
      Else
        .Send
      End If
   End With

   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
   Set objOutlookRecip = Nothing
   Set objOutlookAttach = Nothing

ErrorMsgs:
   If Err.Number = "287" Then
      MsgBox "You clicked No to the Outlook security warning. " & _
      "Rerun the procedure and click Yes to access e-mail " & _
      "addresses to send your message. For more information, " & _
      "see the document at http://www.microsoft.com/office" & _
      "/previous/outlook/downloads/security.asp."
      Exit Function
   ElseIf Err.Number <> 0 Then
      MsgBox Err.Number & " - " & Err.Description
      Exit Function
   End If
End Function

How is the AttachmentPath being stored in the table ?

Thanks

[ranman256]

It looks like it is attaching MANY files from param: AttachmentPath
If this works, then OK.

But if you just want to add 1 file, then all you need is this line: If Not IsMissing(AttachmentPath) Then .Attachments.Add(AttachmentPath)
remove all the extra code,if AttachmentPath is 1 single file.

[recyan]

@ranman,
Appears the same to me.
I am looking from multiple files attachment point of view.
Basically, let us say, I want to attach multiple files which are stored on the disk.
Their paths is in turn, stored in a field in a table.
Eg the value stored in the field in the table ( Type - Memo ).
C;\MyFolder\test1.doc; C:\MyFolder\test2.doc
Will the code work if this value of the field is passed to the function via a form ?

Thanks

[orange]

recyan,

Can you test it?

[recyan]

recyan,

Can you test it?

Unfortunately no. Should be able to do it after 5- 6 hours. Will test & revert.

Was wondering, whether the value
C:\MyFolder\test1.doc;C:\MyFolder\test2.doc will be treated as an array by the code.

Thanks

[June7]

I don't think so. Each attachment must be added by looping and invoking the .Attachments.Add() action on each discrete path.

[recyan]

Would you be able to help with :
How should the file paths be stored so that the code marked in red, specially the IsArray() works ?

Thanks

[June7]

AttachmentPath is an optional argument variable (variant type) of the SendHTMLEmail function. Look at the Function declaration line to see this.

This means the document path(s) are passed to the function when it is called. Since the variable is declared as variant type, objects can be passed. An array is a type of object. The array is compiled by whatever procedure calls the Function.

The function tests if the variable is an array and if it is, loops through the array elements and attaches each document associated with the path. If it is not an array then it is just a single path string and branches to that code.

The issue here is how to compile the array. Are you storing document paths in table?

This function is designed to be universal, so that any number of paths can be passed to the function. But you have to know how to declare and populate an array (which can be done several ways). The function could be modified to not use array. It could instead open a recordset of paths stored in table and loop through recordset. Or FilePicker dialog could be invoked to allow user to navigate to a folder or specific document.

[recyan]

@June,
Thanks for the explanation. Helped me to see things a bit more clearly.

Are you storing document paths in table ?

I have not got down to doing anything beyond seeing the code on the net & trying to understand & then posting here my queries.
There are 2 ways coming to my mind about storing the document paths
1) Have a field in table, say DocsPath & have values stored in it, like C:\My Docs\test1.txt, C:\My Docs\test2.txt
2) Have another table containing the DocsPath separately in separate records

The code posted in Post1 appears to be a working code. I was just wondering how the author is storing the Path values.

Thanks

[June7]

The author doesn't address where the paths are stored, if they are stored, nor how the array would be compiled.

[recyan]

Frustrating.
Will see how it goes, when I test it, may be in a couple of hours.

Thanks

[recyan]

recyan,

Can you test it?

Tested it.
Using Breakpoints ( Debugging ) :
AttachmentPath shows the value "C:\My Docs\test1.txt; C:\My Docs\test2.txt"
It fails at the IsArray(AttachmentPath) & goes directly to the Else part.
Additional info : Moving the cursor to
AttachmentPath(i) shows <Type Mismatch>

Thanks

[June7]

Now what happens with the variables set to one of the paths?

[recyan]

Now what happens with the variables set to one of the paths?

It jumps in to the

.........
Else
                If AttachmentPath <> "" And AttachmentPath(i) <> "False" Then
..............

and then throws up a Type mismatch.
This error appears to be occurring at And AttachmentPath(i) in the above code.

Thanks

[June7]

Try removing: And Attachments(i) <> "False"

Or maybe remove just: (i)

I wondered if that would be an issue when I first saw the code. Doesn't make sense to reference an array element index if it's already determined it's not an array.