VBA Automatic Email Code Glitches

lukascollings

New Member
Joined
Nov 9, 2015
Messages
7
I have an excel workbook full of different spreadsheets that monitor different inventory items. I have written a VBA code that sends an email to the person responsible for an item along with its current inventory and ordering information at the click of a button to notify them in case they need to order more. When I copy this code into the other spreadsheets it still works, however, the formatting changes. This includes the font size, font type, bolded or no bolded text, and even the background color which is the most annoying. Any help with this issue would be greatly appreciated. I have included the code and photos of the emails that were sent below.

Code:
Option ExplicitPrivate Sub CommandButton1_Click()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim FormulaCell As Range
    
    NotSentMsg = " "
    SentMsg = "Sent"


    
    'Set the range with Formulas that you want to check
    Set FormulaRange = Me.Range("H5:H6")


    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value <= FormulaCell.Offset(0, -1) Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                       
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell


ExitMacro:
    Exit Sub


EndMacro:
    Application.EnableEvents = True


    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description
End Sub


Private Sub CommandButton2_Click()
Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    Set Sendrng = Worksheets("Falcon Tubes").Range("A4:H7")
    Set AWorksheet = ActiveSheet
    
    With Sendrng
        .Parent.Select
        Set rng = ActiveCell
        .Select
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
        
        .Introduction = "Hi " & Range("F2").Value & "," & vbNewLine & vbNewLine & "The table below summarizes your current inventory and ordering information. The items highlighted in red are at or are below the minimum thresholds. Please order accordingly."


        With .Item
            .To = Range("F3").Value
            .CC = ""
            .BCC = ""
            .Subject = "Falcon Tubes Ordering Update"
            .Send
        End With
        
    End With
    
    
    rng.Select
End With
 AWorksheet.Select


StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
 
    Call CommandButton1_Click


End Sub

2e49j00.jpg
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
What Office version are you using?

Would you be willing to use an Outlook window to send the messages, instead of the Excel window?

This should give you more control over the process.
 
Upvote 0
Thank you for the links! They were a lot of help! Now the only question I have is how to change the font. When I send the email, the header shows up as times new romans font 12 (which is standard) however, I was hoping to change this to calibiri instead and possibly size 11.

I have tried inserting:

With Selection.Font
.Name = "Arial Black"
.Size = 12

or

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">strbody = "<FONT SIZE = 3>Good Morning;<p>


However nothing has worked so far
Any ideas?
</code>
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top