Posts by Lapot

    Dear all

    Attached form has a User form that opens automatically when you open it, on the big list (ListView1) you can multi select the lines by holding CTRL key down + clicking mouse right button, It will highlight the selected lines then when you click the "Select Invoices" button it will list the selected invoices on the small window (ListView2) at the top. From here when you click the "Multiple Attach" button it will find each selected invoices' directory path at the excel Sheet("Multi") Colum F then convert each of the founded invoices to PDF and one by one it will attach these PDF invoices to one email.

    What I am trying to achieve is merge all the PDF invoices into one document then attach that document to the email. Can this be done?

    Thanks in advance

    Re: Attach multiple documents on one email by using document path

    Thank you pike, it worked perfectly ,many many months' hard work paid off finally thanks to you.

    obviously if the range increased I need to increase the B2:B4 range. Would it be possible to do until the blank cell in B?

    Also it work so slow, is there way to speed it up?

    Kind regards

    Re: Attach multiple documents on one email by using document path

    Hi All
    Finally I have cracked this problem and managed to attach more one than one file on an email (I didn't know it is this easy), the only problem I have is I had to point the exact cell number that had the path address. Is that a way that I can generalise the cell rather than saying B2,B3,B4,B5 ...ect? I want the macro see every cell path in Colum B until the empty cell and attach it to the email as PDF. Can you please help me on that?


    Re: Attach multiple documents on one email by using document path

    Hi all again
    Looks like no one will be able to solve my problem on this, I like to approach it differently, is there a way that I can do this by going into the directory and picking the individual invoices regarding the requested invoice number then attaching it as PDF on to one email

    For example ; “ \\SVR-Storage4\Accounts_Data\CM-Docs\B\P\1\BP1\214\Billref 245680_513897_1.docx “ this directory link can be broken down as C:\\SVR-Storage4\AccountsData\CM-Docs\ B is first letter of the client code and P is the second letter of the client code these 2 letter are folders by itself ,1 is the first number of the client code (this can be 2 or 3 numbers sometimes) this is also a folder , when whole of these folders selected it gives you another folder BP1 which contains the various matters under this client code , on this incident matter folder is 214 where the Bill ref 245680 is stored, the rest of the number is not needed.
    I have seen on some other forums that people managed to attach multiple documents by selecting the directory. Would it be possible to write a code which will pick up the invoice from the directory rather than the document path option (Colum F) as the document path option hasn’t worked?
    Thank you for your time

    Re: Attach multiple documents on one email by using document path

    Hi Apo , thank you for getting back to me , I didn't realise you replied my post. I still not managed to solve this problem.

    I have created another Listview window which I called Listview2. From the first Listview window I select multiple rows then with click of a button I list the selected invoices to another sheet which I call "Multi".
    These selected invoices appear on Listview2 window with below code I try to attach selected rows' invoices to one email but still doesn't work. Could someone help me please?

    this is liestview2 pic


    This is sheet Multi


    this part of the code should make it work but when code run nothing happens

    Set R = Worksheets("Multi").Range("E2", _
    Worksheets("Multi").Range("E" & Rows.Count).End(xlUp))For i = 1 To ListView2.ListItems.Count
    '  If ListView2.ListItems(i).Checked = True Then
    If ListView2.ListItems(i).Selected = True Then
    SearchInv = ListView2.ListItems(i).SubItems(5)
    With R
    .NumberFormat = "0"
    .Value = .Value
    Set fnd = .Find(SearchInv.Value, LookAt:=xlWhole)
    End With

    Dear all

    I'm using the code below, in outlook vba, It converts the File path link to PDF then creates an email, attach a file, and send the email. It works fine, except I can't figure out how to add multiple attachments to a single email?

    My File path is in Column F under Sheet1 as below. Every line relates one document in directory. Here is the sample lines ;

    \\SVR-Storage4\Accounts_Data\CM-Docs\B\P\1\BP1\214\Bill ref 245680_513897_1.docx
    \\SVR-Storage4\Accounts_Data\CM-Docs\C\H\A\CHA116\31\Bill ref 245675_513831_1.docx
    \\SVR-Storage4\Accounts_Data\CM-Docs\C\I\T\CIT13\312\Bill ref 245668_513786_1.docx
    \\SVR-Storage4\Accounts_Data\CM-Docs\C\O\F\COFW6\715\Bill ref 245669_513787_1.docx
    \\SVR-Storage4\Accounts_Data\CM-Docs\H\T\L\HTL1\288\Bill ref 245674_513812_1.docx
    \\SVR-Storage4\Accounts_Data\CM-Docs\H\T\L\HTL1\303\Bill ref 245673_513810_1.docx
    \\SVR-Storage4\Accounts_Data\CM-Docs\L\E\W\LEW23\22\Bill ref 245681_513898_1.docx

    The way it works at the moment ; when you open the excel form it automatically opens a UserForm which has Listview window. if you highlight a row it updates the various TextBoxes over the UserForm, TextBox5 has the invoice number, when you click "Email button" on the UserForm, it finds the whatever invoice number in the TextBox5 and matches with the invoice number under Column B on "invoice Sheet" then activates the path under Column E. The File path will find the invoice in the directory in word format then it will convert it to PDF and attach to an email.

    For multiple attachment I have crated another Sheet which is Sheet1. On the UserForm Listview window I select multiple rows then I click "List" button this will create a list on the Sheet1 for multiple attachments. You should also select multiple invoices on the UserForm list then click "List" button it will create multiple list on the Sheet1 for emailing to see the list

    So what is the best way to achieve by amending below code and get multiple PDF invoice on one email?

    Any help is greatly appreciated.

    Re: Column Y has invoice hyperlink , transfer the link to PDF and attache to an ema

    Can anyone help please?

    Quote from Lapot;730175

    I have also this code to attach the invoices as word document in to an email but I like the invoices attached as PDF.

    Re: Column Y has invoice hyperlink , transfer the link to PDF and attache to an ema

    I have also this code to attach the invoices as word document in to an email but I like the invoices attached as PDF.

    I have a excel sheet which has Hyperlink on Column Y to open invoices in word document. I like to run a macro and open the invoice from the link convert to PDF and attach to an email.

    Below code is for the invoice named MyReport.docx on desktop , so I like to change this code so that invoices comes from Column Y on Excel sheet.

    Is it possible?

    Re: Copying formula on a colum and paste as value

    Quote from JohnCleary;729883


    thank you very much , perfect

    Dear all

    I have a sheet that Column B contains variable data which are formula based, I am trying to copy the values from Column B and paste it to Column C. My Sheet has header on row1 so header will be static.

    Tried various ways but I cant get what I like. I don't want to use record macro option as the data length keep changing.

    Your views will be appreciated.

    Kind regards