+ Reply to Thread
Results 1 to 2 of 2

Need to choose range for .pdf email attachment and scale to a single page

Hybrid View

  1. #1
    Registered User
    Join Date
    04-30-2021
    Location
    Canada
    MS-Off Ver
    365
    Posts
    12

    Need to choose range for .pdf email attachment and scale to a single page

    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,
    Last edited by ABourdages; 06-29-2023 at 08:59 PM. Reason: Solved

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    897

    Re: Need to choose range for .pdf email attachment and scale to a single page

    Try inserting the below code before: ChDir "C:\Alexa"


        ActiveSheet.PageSetup.PrintArea = "$A$1:$S$49"
            With ActiveSheet.PageSetup
                  .PaperSize = xlPaperA4
                  .PrintArea = rngExp.Address(0, 0)
                  .Orientation = xlLandscape
                  .Zoom = False
                  .FitToPagesWide = 1
                  .FitToPagesTall = 1
            End With
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 12-15-2018, 08:27 PM
  2. vba to email after modifying a single sheet from wkbk as a PDF attachment
    By dyoung785 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2016, 09:30 PM
  3. Attachment + range of cells via email
    By danjones121 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-07-2015, 06:58 AM
  4. Email single sheet as an attachment
    By JamesT1 in forum Excel General
    Replies: 8
    Last Post: 08-15-2011, 04:54 AM
  5. email single excel page
    By BongoBill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2009, 02:42 AM
  6. Email a single sheet as attachment ??
    By Matt Stone in forum Excel General
    Replies: 1
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1