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