VBA Loop to create Excel Sheet, save excel sheet to specific folder on computer and generate Outlook 365 Email with attached excel sheet.

  • Hi All


    I am hoping you can assist in how i would be able to writeup a VBA macro code that can resolve the below issue.


    Summary: Looking to create a VBA macro that can check the vins in the attached sample excel sheet “MOND94 – BUGS CERT -Checklist” where it will identify each row through a VBA Loop to determine if I need to follow up load port and ask them using a customized message generated in Microsoft Outlook 365 to send certificates to me for that missing vin for that particular load port.


    Each Vin in Column H would have an identifier next to it in Column I to determine if a certificate needs to be followed up as follows:


    Y = Yes vin needs to be treated and no need to follow up certificate as you already have it on file.


    N = Vin does not need to be treated as it is coming from a non risk country and no need to follow up certificate.


    Blank = Vins need to be checked and certificate for treatment needs to be followed up.



    Here are the below issues currently faced.


    Issue 1: The VBA macro would need to start in sheet ‘Rpt_Discharge_Excel’ in Column H (VIN) row 1 and start searching from top to bottom for the Vin Numbers.


    Next to Column H (VIN) is Column I (Bugs Treated) if cell is blank then I want the macro to generate a sheet for that load port in Column C pasting that entire row for that missing blank VIN in a sperate load port sheet.


    Each load port would have an email address that I need to send to as found in the Load Port Sheet and also I need to save the load port sheet in a centralized folder in documents (It just needs to be in 1 folder) so that I can refer back to it if needed. Please note if I were to run the macro again I would like for the sheet when saved in a folder not to overwrite the current excel file but rather save the latest sheet based on date and time.


    Issue 2: Would like to have an Outlook 365 Email generated with the attached excel Load Port sheet for those missing vins with customs message saying.


    “Hi All


    Please find attached excel sheet to send us certificates for the following vins”



    Issue 3: The macro needs to know when the last row is so that it doesn’t keep looping infinitely and needs to stop at the last row in sheet ‘Rpt_Discharge_Excel’



    Notes:

    • This macro would need to use some kind of do until loop or do loop, with if and else statements.
    • Vins can range from 17 digits to 4 digits.
    • The attached excel sheet is just sample data.


    Please advise if you can assist?


    Best regards


    Dan