VBA - How to string two commands into one macro

Florida1510

New Member
Joined
Mar 13, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
Folks,

I need some assistance I'm trying to add a second request to my existing macro to either screenshot or copy a range of cells from the ACC Data Input tab - Sheet1 (Range A1:D21). I would like for the range to be placed right under the wording "High Usage SIMs - Average SIM Usage - X.XXMB" which is part of the HTMLBody of this code. For the life of me I can't get it to work. Any help would be truly appreciated.

Sub sendEmail()

' ******************************** Variable Declaration ********************************
' Dim Req_id As Integer, nextReq_id As Integer, x As Integer, y As Integer, rownumber As Integer, _
' colnumber As Integer, finalRow As Integer, finalColumn As Integer, errCounter As Integer

' Dim TestFreq As String, month As String, pvt As PivotTable
Dim RangeToCopy As Range
Dim wsACC As Worksheet, wsContacts As Worksheet
Dim OutApp As Object, OutMail As Object
Dim lRow As Long
Dim PEmailId As String, CompanyName As String, SEmailId As String, CycleClose As String, Now As String
Dim FilesToOpen

' ******************************** Setting variables ********************************

Set wsACC = ThisWorkbook.Sheets("ACC")
Set wsSheet1 = ThisWorkbook.Sheets("ACC Data Input")
Set wsContacts = ThisWorkbook.Sheets("Contacts")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.createitem(0)


' ******************************** processing start ********************************

'Setting width for month column to avoid it being displayed as #### in outlook

CompanyName = wsContacts.Range("A2")
PEmailId = wsContacts.Range("B2")
SEmailId = wsContacts.Range("C2")
CycleClose = wsContacts.Range("D2")



With wsACC
.Activate

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Set RangeToCopy = .Range("A1:L" & lRow + 1)
Columns("A:L").AutoFit


' ********************************** Prepare the email reminder **********************************
Set OutMail = OutApp.createitem(0)
With OutMail
.Display 'or use .Send
.To = PEmailId
.CC = SEmailId
' .BCC = ""
' .attachments.Add

.Subject = CompanyName & " ESS Weekly Update " & Date
.HTMLBody = "<font size=""3""> <p><b>Hello All" & ",<p>" & _
"Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". <p>" & _
"Attached is the report for the high data usage devices for the week.<p>" & _
"The table below reflects the current cycle to date usage through " & Date & " and current rate plan allocations.</font>" & RangetoHTML(RangeToCopy) & _
"<p><font color=""grey""><em> * Usage will continue to be monitored through the cycle.</em></font>" & _
"<font size=""3""> <p><b>High Usage SIMs - Average SIM Usage - X.XXMB" & "<p>" & "<font size=""3""> <p><b>Maintenance" & "<p>" & _
"<font size=""3""> <p><b>Billing" & "<p>" & _
"<font size=""3""> <p><b>Items of Note" & "<p>" & _
"<font size=""3""><p> Kindly advise if you have any questions or need further information.</p></font></b>" & OutMail.HTMLBody


End With

End With


ExitHandler:
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub


End Sub
 

Attachments

  • Screenshot of data.png
    Screenshot of data.png
    205.8 KB · Views: 3

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Florida1510

New Member
Joined
Mar 13, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
Anthony,

I'm back I'm sorry is there any reason why the code inputs the information twice into my email (see image)?

******************************** processing start ********************************

'Setting width for month column to avoid it being displayed as #### in outlook

CompanyName = wsContacts.Range("A2")
PEmailId = wsContacts.Range("B2")
SEmailId = wsContacts.Range("C2")
CycleClose = wsContacts.Range("D2")



With wsACC
.Activate

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Set RangeToCopy = .Range("A1:L" & lRow + 1)
Columns("A:L").AutoFit


' ********************************** Prepare the email reminder **********************************
Set OutMail = OutApp.createitem(0)
With OutMail
.Display 'or use .Send
.To = PEmailId
.CC = SEmailId
' .BCC = ""
' .attachments.Add

.Subject = CompanyName & " ESS Weekly Update " & Date
.HTMLBody = "<font size=""3""> <p><b>Hello All" & ",<p>"
.HTMLBody = .HTMLBody & "Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". <p>"
.HTMLBody = .HTMLBody & "Attached is the report for the high data usage devices for the week.<p>"
.HTMLBody = .HTMLBody & "The table below reflects the current cycle to date usage through " & Date & " and current rate plan allocations.</font>" & RangetoHTML(RangeToCopy)
.HTMLBody = .HTMLBody & "<p><font color=""grey""><em> * Usage will continue to be monitored through the cycle.</em></font>"
.HTMLBody = .HTMLBody & "<font size=""3""> <p><b>High Usage SIMs - Average SIM Usage - X.XXMB" & "<p>" & "<br>" & RangetoHTML(wsSheet1.Range("A1:D21"))
.HTMLBody = .HTMLBody & "<font size=""3""> <p><b>Maintenance" & "<p>"
.HTMLBody = .HTMLBody & "<font size=""3""> <p><b>Billing" & "<p>"
.HTMLBody = .HTMLBody & "<font size=""3""> <p><b>Items of Note" & "<p>"
.HTMLBody = .HTMLBody & "<font size=""3""><p> Kindly advise if you have any questions or need further information.</p></font></b>" & OutMail.HTMLBody


FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Excel Files (*.xlsx), *.xlsx", _
MultiSelect:=False, Title:="Select file to look data up")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
.attachments.Add (FilesToOpen)


End With

End With
 

Attachments

  • Duplication Image.png
    Duplication Image.png
    50.9 KB · Views: 4

Florida1510

New Member
Joined
Mar 13, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
Anthony,

Disregard I had a good teacher I figured it out. Thanks again.

Owen
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

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
Top