How to Create a PDF

Useful function that creates a PDF in VBA.

Usage:
Creating a PDF from the active worksheet.

    Dim myFile As String
    'Save PDF being generated to Temp location
    myFile  = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp")) & "myPDF.pdf"
    fileToSend = Create_PDF(ActiveWorkbook.ActiveSheet, myFile, True, False)

The code:

Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
                 OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test to see if the Microsoft Create/Send add-in is installed.
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

        If FixedFilePathName = "" Then
            'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
                  Title:="Create PDF")

            'If you cancel this dialog, exit the function.
            If Fname = False Then Exit Function
        Else
            Fname = FixedFilePathName
        End If

        'If OverwriteIfFileExist = False then test to see if the PDF
        'already exists in the folder and exit the function if it does.
        If OverwriteIfFileExist = False Then
            If Dir(Fname) <> "" Then Exit Function
        End If

        'Now export the PDF file.
        On Error Resume Next
        Myvar.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish
        On Error GoTo 0

        'If the export is successful, return the file name.
        If Dir(Fname) <> "" Then Create_PDF = Fname
    End If
End Function

Sending Email Through SMTP (Using CDOSYS)

Very handy function for sending emails through SMTP using VBA.

Sub Mail_SMTP(StrAttachment As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
    
    On Error GoTo ErrHandler
    
    Dim smtp_username As String
    Dim smtp_password As String
       
    smtp_username = ""
    smtp_password = ""
    
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mycompany.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = smtp_username
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = smtp_password
        .Update
    End With

    With iMsg
        Set .Configuration = iConf
        .To = StrTo
        .CC = ""
        .BCC = ""
        .From = getUsername() & "@mycompany.com"
        .Subject = StrSubject
        .TextBody = StrBody
        .AddAttachment StrAttachment
        If Send = True Then
            iMsg.Send
        Else
            iMsg.Display
        End If
    End With
    
    If Err.Number = 0 Then
        MsgBox "Email sent to: " & StrTo, vbInformation, "Email"
    End If
    
ErrExit:
    Exit Sub

ErrHandler:
    MsgBox "Unable to send email. Please make sure you are connected to the internet."
    Resume ErrExit
    
End Sub

Sending an Email Through Outlook

This handy function sends an email through Excel using VBA.

Sub Mail_Outlook(StrAttachment As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
    
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error GoTo ErrHandler
    
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add StrAttachment
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    If Err.Number = 0 Then
        MsgBox "Email sent to: " & StrTo, vbInformation, "Email"
    End If

ErrExit:
    Set OutMail = Nothing
    Set OutApp = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox "Description     : " & Err.Description & vbNewLine & _
           "Error Number    : " & Err.Number, vbOKOnly, "Error"
           
    Resume ErrExit
    
End Sub

Setting Tab Order In Excel

I found a couple of different ways to do this but by far this was the less obtrusive one.

Please note that the left section of the formula checks your current location and the right section dictates your next location.

Private Sub Worksheet_Change(ByVal Target As Range)
    'jump B2->B3->B4->B5->B6->B7
    If Target.Cells.Address = "$B$1" Then Range("B2").Select
    If Target.Cells.Address = "$B$2" Then Range("B3").Select
    If Target.Cells.Address = "$B$3" Then Range("B4").Select
    If Target.Cells.Address = "$B$4" Then Range("B5").Select
    If Target.Cells.Address = "$B$5" Then Range("B6").Select
    If Target.Cells.Address = "$B$6" Then Range("B7").Select
End Sub