Hello,
I've written some vba code to trigger when an active x button is pressed. Pretty standard e-mail code. It also makes a pdf of the active worksheet and attaches it to the email.
I would like to restrict the pdf to the range (a1:s49) while also scaling to a single page.
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim emailRng As Range, cl As Range
Dim sTo As String
Dim sto2 As String
Set emailRng = Worksheets("Donn?es").Range("B17:B22")
Set emailRng2 = Worksheets("Donn?es").Range("H17:H22")
For Each cl In emailRng
sTo = sTo & ";" & cl.Value
Next
For Each cl In emailRng2
sto2 = sto2 & ";" & cl.Value
Next
sTo = Mid(sTo, 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
ChDir "C:\Alexa"
Activeworksheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Alexa\OneDrive\Desktop\Horaire.pdf", ncludeDocProperties:=True _
, IgnorePrintAreas:=False, OpenAfterPublish:=False
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0
With OutMail
.To = sTo
.CC = sto2
.BCC = ""
.Subject = "Horaire " & Format(Date, "dd-mm-yyyy")
.Body = "Bonjour," & vbNewLine & _
"" & vbNewLine & _
"Voici l'horaire pour le mois prochain. " & vbNewLine & _
"" & vbNewLine & _
"Cordialement," & vbNewLine & _
"Karine Archambault"
.Attachments.Add "C:\Users\Alexa\OneDrive\Desktop\Horaire.pdf"
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Any help is greatly appreciated.
Cheers,
Bookmarks