Posts by quanziee

    I seem to have forgotten to add the file path it seems. I changed it now.

    Code
    'PdfFile = destFolder & Sheets("Hotel Booking").Range("C4") & " " & Sheets("Hotel Booking").Range("C11") & " " & "Hotel Booking.pdf"
    'to
     PdfFile = PdfFile & destFolder & Sheets("Hotel Booking").Range("C4") & " " & Sheets("Hotel Booking").Range("C11") & " " & "Hotel Booking.pdf"


    So the complete code for the attachment is


    If I run the macro now, it will send the email without any attachments and will give me an error on the line "Kill PdfFile" saying "File not found". The result on the immediate window is "https://d.docs.live.net/5b4e348d0c77a54e/Desktop/School Work/Crew Scheduling with CDO EVA Air BR225 Hotel Booking.pdf".



    When I try changing the line

    Code
    'PdfFile = ActiveWorkbook.FullName
    'to
    PdfFile = ActiveWorkbook.Path


    and keep the rest of the code the same, it will give me an error on this line

    Code
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


    saying "Invalid procedure call or argument". The result on the immediate window is "https://d.docs.live EVA Air BR225 Hotel Booking.pdf". I'm not sure why it doesn't display the full path name. When I created another macro only with the line "Msgbox Activeworkbook.Path", it will display "https://d.docs.live.net/5b4e348d0c77a54e/Desktop/School Work".



    When I try running the macro on an unsaved workook, I will get an error on the line

    Code
    PdfFile = PdfFile & " " & destFolder & Sheets("Hotel Booking").Range("C4") & " " & Sheets("Hotel Booking").Range("C11") & " " & "Hotel Booking.pdf"


    saying "Subscript out of range".

    For the sake of the file name to be constant, both cells "C4" and "C11" will have the same values in both workbooks. "C4" will have "EVA Air", and "C11" will have "BR225".


    First run on the activeworkbook: Immediate window displays "EVA Air BR225 Hotel Booking.pdf". This is the intended file name.
    Second run on an unsaved workbook: Immediate window displays "EVA Air BR225 Hotel Booking.pdf".


    The same error appears on both runs. "The specified protocol is unknown."

    I also forgot to mention that the attachment portion of my code has changed. Here is the revised one after I add the "debug.print" line


    I guess you obfuscated your smpt server name and email address. The server governs what ports it will accept.


    As for the attachment deal, you can use Debug.Print Dir(pdffile) or in one line of code:

    Code
    If Dir(PdfFile) <> "" Then .AddAttachment (PdfFile)


    I don't seem to understand what you mean here. The error I'm getting right now says ""The specified protocol is unknown." on

    Code
    .AddAttachment PdfFile


    The correct file name is shown in the immediate window.

    Hey, the CDO part of my code works fine. It could send emails without attachments. I added the

    Code
    Debug.Print pdfFile

    line and when I checked the immediate window, it shows the correct file name. However, now I'm getting an error on this line

    Code
    .AddAttachment PdfFile

    . The error says "The specified protocol is unknown."


    Here are the details for my SMTP part:

    Code
    With Flds
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp-mail.outlook.com"
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
                .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxx"
                .Update
            End With

    I'm trying to create a macro whereby the print area of a sheet, "Hotel Booking" is attached as a PDF file to an email. The email will be created using CDO and not Outlook Application. Everything else in my code works except for the attachment. It will say file not found and will not attach anything to the email.


    Here's my code that only focuses on the email attachment:


    Here's the whole code:


    Also asked this question here:
    http://www.vbaexpress.com/foru…s-PDF&p=383039#post383039
    https://stackoverflow.com/ques…taching-print-area-as-pdf

    Hi, I want to create and display emails with attachments using a web browser. I want a web browser(google chrome, internet explorer etc.) or perhaps even the user's default web browser to pop up when they run the macro. My current code only manages to open the outlook application and display the email. I have been reading up on CDO but I don't quite understand it or how it works.


    Here's my current code:


    Also asked this here:
    http://www.vbaexpress.com/foru…owser&p=382288#post382288

    Hi, complete beginner with VBA here. I want to send an email using VBA and attach multiple sheets from the workbook onto the same email, as separate attachments. I found some codes online that attaches a single sheet as a PDF but some of them do not work for me. I'm using Excel-2016. I found one that works but it takes a really long time for the email display to pop up and the code is in no means optimized. Also I'm unable to attach multiple sheets as separate PDFs too.


    Here's my code:


    Strapped of time and also asked this question here:
    http://www.vbaexpress.com/foru…ments&p=381963#post381963

    I want to copy and paste a bunch of information from sheet "A" to sheet "B" and I want to delete the information from sheet "B" after a certain time frame. However, the macro should be able to run multiple times and subsequent information from sheet "A" should be pasted below the current information pasted on sheet "B" that has not been deleted yet. My current code can do this but I have a problem where by if I paste more information on sheet "B" the second time, the deleting function will mess up.




    Code
    Sub DelayMacro()
    
    
    Application.OnTime Now() + TimeValue("00:00:10"), "Delete"
    
    
    End Sub


    EDIT:
    Since no one has answered this on this site yet and I'm short of time, I asked this question on these sites too:
    http://www.vbaexpress.com/foru…timer&p=381929#post381929
    https://stackoverflow.com/ques…el-vba-delete-after-timer

    Hi, I want to check a range of cells for a text, text is in one of the cells, and I want excel to be able to identify which row the text is in. Range is from columns "AB:BB". I just put 500 in the loop just to count down the rows. The range is not beyond row 500. Thanks.


    Here is my current code: