EMAIL_MODULE - 20151017

The e-mail module, once it is imported together with the log module (all the modules require the log module, since all the modules generate log), abstracts the complexity of sending an Oulook e-mail in one simple and straightforward function. Of course sending an e-mail in VBA is not the most complex task you will ever perform, but it is complicated enough to be simplified as it is in this module. So please download the module itself (the full code is depicted below) or download the workbook that contains it (and a lot more).


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'DOWNLOAD

'File Name = EMAIL_MODULE.bas

'Link to donwload:
' https://www.dropbox.com/s/p5lknx9qr6mwl9a/EMAIL_MODULE.bas?raw=1

'File Name= VBA_MODULES_43.xlsm (file that has the log_module and the email_module ready to be used)

'Link to donwload:
' https://www.dropbox.com/s/pxxwgap8hl7m9xw/VBA_MODULES_43.xlsm?raw=1

'Raw Code:
'----------------------------------------------------------------------------------------------------------------------
'Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' @module EMAIL_MODULE
' @author Yan França Tosta
' @version 43
' @since 06/2015
' @requires the LOG_MODULE
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'
' @function: Send_Email as boolean
' @description: sends e-mail using outlook application
' @param ByVal Receiver As String: the recipient of the e-mail
' @param ByVal Subject As String: the subject of the e-mail
' @param Optional ByVal Message As String = vbNullString: the HTML message body of the e-mail
' @param Optional ByVal CC As String = vbNullString: the recipients in copy
' @param Optional ByVal File_Path As String = vbNullString: the attachment file path
' @param Optional ByVal Display As Boolean = False: if true, displays the send screen; sends the e-mail automatically otherwise
' @return Boolean: true if success, false otherwise
'
Public Function Send_Email(ByVal Receiver As String, _
                           ByVal Subject As String, _
                           Optional ByVal Message As String = vbNullString, _
                           Optional ByVal CC As String = vbNullString, _
                           Optional ByVal File_Path As String = vbNullString, _
                           Optional ByVal Display As Boolean = False) As Boolean
                           
    Dim outlookApplication As Object
    Dim outlookMail As Object
    Dim function_name As String
    
    function_name = "EMAIL_MODULE.Send_Email"
    LOG_MODULE.LOG_PRINT function_name
    
    LOG_MODULE.LOG_PRINT function_name, "Sending mail with subject '" _
                        & Subject & "'  to '" & Receiver & "'."
                        
    On Error GoTo error_1000
        LOG_MODULE.LOG_PRINT function_name, "Checks whether the Outlook is open. If it is not, it tries to opens a new instance"
        Set outlookApplication = GetObject(, "Outlook.Application")
        If outlookApplication Is Nothing Then
            Set outlookApplication = CreateObject("Outlook.Application")
        End If
        
        Set outlookMail = outlookApplication.CreateItem(0)
        
        With outlookMail
        
            .To = Receiver
            .CC = CC
            .Subject = Subject
            .HTMLBody = Message
            
            If Not File_Path = vbNullString Then
                .Attachments.Add File_Path
            End If
            
            If Display Then
                LOG_MODULE.LOG_PRINT function_name, "Opening Display to Send the e-mail"
                .Display
            Else
                LOG_MODULE.LOG_PRINT function_name, "Sending the e-mail without opening the display"
                .send
            End If
            
        End With
        
        Send_Email = True
        LOG_MODULE.LOG_PRINT function_name, "E-mail sent"
        LOG_MODULE.LOG_PRINT function_name, 0
        Exit Function

error_1000:
    LOG_MODULE.LOG_PRINT function_name, Err.Description
    LOG_MODULE.LOG_PRINT function_name, 1
    Send_Email = False
    Exit Function

End Function


'
' @function: Send_Email_Display as boolean
' @description: displays the outlook send e-mail screen
' @return Boolean: true if success, false otherwise
'
Public Function Send_Email_Display() As Boolean

    Dim function_name As String
    
    function_name = "EMAIL_MODULE.Send_Email_Display"
    LOG_MODULE.LOG_PRINT function_name
    
    On Error GoTo error_1000:
        
        Send_Email_Display = Send_Email(vbNullString, vbNullString, vbNullString, vbNullString, vbNullString, True)
        
        LOG_MODULE.LOG_PRINT function_name, 0
        Exit Function
        
error_1000:
    LOG_MODULE.LOG_PRINT function_name, Err.Description
    LOG_MODULE.LOG_PRINT function_name, 1
    MsgBox "Unable to open the e-mail display", , "ERROR"
    Send_Email_Display = False
    Exit Function
End Function

LOG_MODULE - 20151017

DOWNLOAD

File Name = LOG_MODULE.bas

Link for donwload: https://www.dropbox.com/s/6h0g2m9nu2huc6k/LOG_MODULE.bas?raw=1

Raw Code:
----------------------------------------------------------------------------------------------------------------------

Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' @module LOG_MODULE
' @author Yan França Tosta
' @version 35
' @since 06/2015
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'
' @function: Log_File_Name
' @description: this function returns the standard log file path
' @returns: String with the path of the log file
' @exception: Prints message to immediate window
'
Public Function Log_File_Name() As String
   
    Dim currentDate, fileName, fullFileName, extension As String
   
    On Error GoTo e1000
        fileName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
        currentDate = Date
        currentDate = Format(currentDate, "yyyy-mm-dd")
        extension = ".log"

        fullFileName = fileName & "_" & _
                       currentDate & _
                       extension
       
        Log_File_Name = Log_Folder + "\" + fullFileName
        Exit Function
e1000:
    Debug.Print "Log file name creation failed"
    Exit Function
End Function

'
' @function: Log_Folder
' @description: this function returns the standard log folder path
' @returns: String with the name of the log folder
' @exception: NO
'
Private Function Log_Folder() As String
    On Error Resume Next
        Log_Folder = "C:\TEMP"
    Exit Function
End Function


'
' @function: Log_Mode
' @description: this function receives the log mode name and returns the code of the mode of the log printing
'               There are 3 log modes: ON, OFF and DEBUG
' @param: Optional Log as String: the name of the log mode
' @returns: Integer with the defined log mode
' @exception: NO
'
Private Function Log_Mode(Optional ByVal Log As String = "debug") As Integer
   
    On Error GoTo e1000:
       
        Log = UCase(Log)
       
        If Log = "ON" Or Log = "ERROR" Or Log = "1" Then
            Log_Mode = 1
            Exit Function
        ElseIf Log = "DEBUG" Or Log = "2" Then
            Log_Mode = 2
            Exit Function
        Else
            Log_Mode = 0
            Exit Function
        End If
   
e1000:
    Exit Function
End Function


'
' @function: logInformation
' @description: receives a message and prints it to the log file
' @param: Byval LogMessage as string - the message to be printed
' @exception: Displays message box
'
Private Sub logInformation(LogMessage As String)
    Dim LogFileName As String
On Error GoTo error_1000:
    LogFileName = Log_File_Name
    Dim FileNum As Integer
        FileNum = FreeFile ' next file number
        Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
        Print #FileNum, LogMessage ' write information at the end of the text file
        Close #FileNum ' close the file
     
        Exit Sub

error_1000:
    On Error GoTo error_2000
        MkDir Log_Folder
        Exit Sub
     
error_2000:
    MsgBox "The log folder '" + Log_Folder() + "' does not exist." + vbNewLine + _
           "Please create it manually. (It may be a permission issue)"
    Exit Sub
End Sub

'
' @function: Log
' @description: receives messages and prints the correspondent log message to the log file
' @param: Optional Byval Title as String - the title of the log message or a log code message
' @param: Optional Byval Message as String - the log message
' @exception: NO
'
Public Function Log(Optional ByVal Title As String = vbNullString, _
                    Optional ByVal Message As String = vbNullString)
    On Error Resume Next
        Log_Print Title, Message
        Exit Function
End Function

'
' @function: Log_Print
' @description: receives messages and prints the correspondent log message to the log file
' @param: Optional Byval Title as String - the title of the log message or a log code message
' @param: Optional Byval Message as String - the log message
' @exception: Prints message to immediate window
'
Public Sub Log_Print(Optional ByVal Title As String = vbNullString, _
                     Optional ByVal Message As String = vbNullString)
    Dim Text As String
    Dim logMode As Integer
 
    On Error GoTo error_1000
     
        If Title = vbNullString And Message = vbNullString Then
            Exit Sub
        End If
     
        logMode = Log_Mode()
     
        If logMode = 0 Then
            Exit Sub
        End If
     
        If Len(Message) > 200 Then
            Message = Left(Message, 250)
        End If
     
        ' Normal log mode - it only prints errors
        If logMode = 1 Then
            If Message = "1" Or Message = "ERROR" Then
                Application.StatusBar = "ERROR"
                Text = Title + " - " + "ERROR " + " - " + "EXIT FAILURE"
            Else
                Exit Sub
            End If
        ' debug mode
        ElseIf logMode = 2 Then
            If Message = "1" Or Message = "ERROR" Then
                Text = Title + " - " + "ERROR " + " - " + "EXIT FAILURE"
            ElseIf Message = "0" Or Message = "SUCCESS" Then
                Application.StatusBar = "SUCCESS"
                Text = Title + " - " + "EXIT SUCCESS"
            Else
                If Message = vbNullString Then
                    Text = Title
                Else
                    Application.StatusBar = Message
                    Text = Title + " - " + Message
                End If
            End If
        End If
        logInformation Format(Now, "yyyy-mm-dd hh:mm") & " - " & Text
        Exit Sub

error_1000:
    Debug.Print "Unable to log"
    Exit Sub
End Sub


'
' @function: Open_Log
' @description: opens the log file using Notepad.exe
' @exception: Display Message Box
'
Public Sub Open_Log()
 
    Dim pid As Double
    Dim LogFileName As String
 
    On Error GoTo error_1000
        LogFileName = Log_File_Name
        pid = Shell("notepad " + LogFileName, vbMaximizedFocus)
        Exit Sub
error_1000:
    MsgBox "Unable to open '" + Log_File_Name + "' file."
    Exit Sub
End Sub

MAIN

Be welcome to the "Excel And Yan" blog.

This blog is intended to all those who want to become experts in Excel and VBA.

What makes this blog different from the others is that we do not intend to solve little problems individually, but rather make Excel so easy to use and to program, that the user will be able to think Excel as powerful tool and VBA as a "ready-to-go" programming language not too far from great languages such as Python or Ruby.

The way we try to achieve such a pretentious objective is to use the most flexible tools Excel provide, to use all the best practices all the time, and to create the maximum of abstraction layers upon VBA, in a way that even the most complex problem can be easily addressed using just a handful of intuitive code lines.

Our approach is to deal with classes of problems and not individual problems and to try to compensate VBA limitations with abstraction and simplification.We don't think in terms of macros or functions, but rather in terms of modules and classes. This, for those who are used to the classical Excel macro approach might be scared at first, but we promise it we'll be much easier our way (and much more fun).

Another approach we use is to treat every single VBA script as a professional application. This means that the same principles we apply when programming, for example, a Java application should be used when programming in VBA. We believe that a, say, 10% overhead to implement Excel and VBA best practices for every application we deliver is rewarding and that every application is a critical one - this means that we don't like unexpected flaws.

The blog is being developed with the classical articles structure, this means that we will choose a topic, write an article about it and will make the correspondent code/workbook available for download. We will also publish our modules as posts, so one can easily find it in the blog.

We also are VERY open to collaboration and we really would like people to improve our VBA codes.

All That being said, let's begin!