Consulting

Results 1 to 2 of 2

Thread: Newbie needs help please

  1. #1
    VBAX Newbie
    Joined
    Sep 2020
    Posts
    2
    Location

    Newbie needs help please

    Hi All
    I am a newbie at VBA coding and having trouble getting my head around it. I have looked at other posts but not sure which one suits what I need, so I apologize if this question has already been answered previously.
    I did write a very small bit of code that was working but it wasn't doing what I wanted it to do, so I tried adding in more information, still works and still doesn't do what I need it to do. I guess I am missing a lot of information.
    I am wondering if anyone may be able to look at my code and see what is missing and guide me in the right direction. I have used little emojis to show which parts are working.
    Thank you so much

    What I want my code to do

    1. Select the email address in D16 on sheet 1 (“PO Home”)
    2. Select the worksheet (“PO Home”)
    3. Hide data in range (K:P)
    4. Copy sheet
    5. Convert the sheet to PDF format
    6. Send as an attachment
    7. Have a Subject title – “Purchase Order” (Located in “E3”)
    8. Have a body text –
      1. Dear __Use the name of the person in Cell (“D11)
      2. “Please supply the items on the attached Purchase Order
      3. Thank you Sign with “Andy Munday JLM Homes”

    9. Send the email
      1. to address in D16,
      2. confirm the correct address,
      3. delivery / read receipt

    10. Do not save the file



    Sub Send_Worksheet()
        
        Dim semail_Recipients As String
                 range("D16").Select
           
        Dim Title As String
                 range("E3").Select
             
        Dim PDFfile As String
                ActiveSheet.ExportAsFixedFormat xlTypePDF 'convert to PDF format
           
        Dim Wb As Workbook
                Sheets("PO Home").Select
                ThisWorkbook.Worksheets("PO Home").Copy 'copy sheet 1 before sending
               
                Worksheets("PO Home").range("K:P").EntireColumn.Hidden = True
                With ActiveWorkbook 'send email
                       .SendMail range("D16")
                       .Subject = Title
                       .Body = "Hello, please supply items as per attached Purchase Order"  & "Thank you" & "Myname"
                       .Close SaveChanges:=False 'dont save changes
               End With
    End Sub
    Last edited by SamT; 02-20-2021 at 09:05 AM.

  2. #2
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Please refer below, may be helpful for you
    VBA Express : Excel - Email a Worksheet or Workbook Using Outlook

Posting Permissions

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