macro to send emails from slicer

bb60

New Member
Joined
Mar 2, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all need a help here.

So i have a an excel dashboard about a team performance, i have a macro to set excel to send a "screen shot" of the performance dashboard for the agent that is selected on the slicer,

but the thing is, i have around 200 agents and doing this one by one is a little by painful.

Is there any way to automate the process ?

Like if the macro was selecting automatically agent by agent on the slicer, and sending the screen shoot of their dashboard of performance?

where is my macro

Sub RangeToOutlook_Single()


'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector

'Declare Word Variables
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range

'Delcare Excel Variables
Dim ExcRng As Range

On Error Resume Next

'Get the Active instance of Outlook if there is one
Set oLookApp = GetObject(, "Outlook.Application")

'If Outlook isn't open then create a new instance of Outlook
If Err.Number = 429 Then

'Clear Error
Err.Clear

'Create a new instance of Outlook
Set oLookApp = New Outlook.Application

End If

'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)
Set Sh = ThisWorkbook.Sheets("Pivot SC")


'Create an array to hold ranges
Set ExcRng = Sheet4.Range("A1:R65")


With oLookItm

'Define some basic info of our email
.SentOnBehalfOfName = ""
.To = Sh.Range("A78").Value
.CC = Sh.Range("A79").Value
.Subject = "Metrics from January"
.Body = " "

'Display the email
.Display

'Get the Active Inspector
Set oLookIns = .GetInspector

'Get the document within the inspector
Set oWrdDoc = oLookIns.WordEditor


ExcRng.Copy

'Define the range, insert a blank line, collapse the selection.
Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
oWrdRng.Collapse Direction:=wdCollapseEnd

'Add a new paragragp and then a break
Set oWrdRng = oWdEditor.Paragraphs.Add
oWrdRng.InsertBreak

'Paste the object.
oWrdRng.PasteSpecial DataType:=wdPasteMetafilePicture


End With


End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,992
Members
415,873
Latest member
fuulhouse

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