+ Reply to Thread
Results 1 to 10 of 10

VBA: Filter, copy, paste to outlook email with text, then repeat

  1. #1
    Registered User
    Join Date
    04-12-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    9

    VBA: Filter, copy, paste to outlook email with text, then repeat

    Hi, I have been finding several macro codes and editing them to my needs but i just could not achieve what I wanted, mainly due to how noob I am at vba.

    My aim is to create a code that filters by the cost centre, then copy the filtered columns B to E and paste to a new outlook message.

    My outlook message contains the words except the variable:

    "Hi,

    Please be reminded bla bla bla....QWE (the variable that changes according to the cost centre)

    (Followed by the filtered table)

    Thank you.

    Regards,
    bla bla"


    I also want the outlook message to have the recipients on it.


    Then the entire process continues for the next cost centre, OCP, and next cost centre, CBK. The email text will be the same except for the variable mentioned above.

    The attached is a picture of my excel spreadsheet.

    Untitled1.jpg


    *Note that all information are fictional in this case.

    Thank you very much in advance for reading this and assisting me to solve it! Will anyone be able to advise how to start off learning vba as well?

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    To send a range to the body of an email message, you have to change it to HTML. You can do that using Ron de bruin's code located here.

    As far as the selection of data, you could:
    • Manually filter the data.
    • Add a combobox on the sheet with filtering code.
    • Add a userform and add more selections, like recipient, filter, etc.


    If you upload a sample workbook, we can give more specific help.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    04-12-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Hi, ok my workbook is as per below. And the word document shows an example of how it should look like for every email created. I wan a macro that can display as many emails as there are as many cost centres.

    My aim is to create a code that filters by the cost centre, then copy the filtered columns B to E and paste to a new outlook message.

    My outlook message contains the words except the variable:

    "Hi,

    Please be reminded bla bla bla....QWE (the variable that changes according to the cost centre)

    (Followed by the filtered table)

    Thank you.

    Regards,
    bla bla"


    I also want the outlook message to have the recipients on it (may omit this)


    Then the entire process continues for the next cost centre, OCP, and next cost centre, CBK.The email text will be the same except for the variable mentioned above.

    1 important thing is i wan to merely display the email, instead of auto sending it out.


    Thanks so much!
    Attached Files Attached Files

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Where are the recipients? Is there a list on a sheet somewhere?

  5. #5
    Registered User
    Join Date
    04-12-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Hi, think i will give the recipients a miss. Cos it varies everytime.

  6. #6
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Look at ribbon, next to home.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-12-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Hi,sorry, turns out the recipients is kind of fixed. how do i include fixed recipients for each cost centre? Like in the workbook attached. Perhaps a list of emails on sheet 2.

    Is there any way that outlook can search its address book and put it as one of the recipients of the email based on name? Like for example, i have "sally" in cell A1, the macro picks this up and addresses the email to sally.
    Attached Files Attached Files
    Last edited by z_eeen; 05-12-2014 at 11:34 AM.

  8. #8
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Quote Originally Posted by z_eeen View Post
    Hi,sorry, turns out the recipients is kind of fixed. how do i include fixed recipients for each cost centre? Like in the workbook attached. Perhaps a list of emails on sheet 2.
    Check out the attachment.
    The list is dynamic; as many workcentres and as many CC. The second column is the TO. Any number of columns from three on is CC.


    Is there any way that outlook can search its address book and put it as one of the recipients of the email based on name? Like for example, i have "sally" in cell A1, the macro picks this up and addresses the email to sally.
    I appears that this can be done, but I'm not that skilled with Outlook. If you want to pursue this, I suggest you start another thread.
    Attached Files Attached Files
    Last edited by Tinbendr; 05-12-2014 at 02:57 PM.

  9. #9
    Registered User
    Join Date
    04-12-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    Hi, can i ask 1 more thing? The code doesn't seem to create the email for the first cost centre, QWE, when i test it. I think it has something to do with this part:

    'This section allows you to start the code
    'from the Activecell. So, if the code is
    'interrupted for some reason, you can start
    'over at the Activell.

    If ActiveCell.Row = 1 Then
    StartRow = 2
    Else
    StartRow = ActiveCell.Row
    End If

    'Iterate cells to get Unique list.
    For A = StartRow To LastRow
    On Error Resume Next
    Unique.Add CStr(.Range("A" & A)), CStr(.Range("A" & A))
    On Error GoTo 0
    Next





    Not very sure what is an activecell.....

  10. #10
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: VBA: Filter, copy, paste to outlook email with text, then repeat

    I wrote that section anticipating that it might be a long list. So, as the comments in the code explains, the code determines which row is active and starts from there. I did this just in case there was a long list, and for whatever reason m the code was interrupted, then you could pickup again without sending the entire list.

    If you are confident that this won't be an issue, then comment out as follows:
    Please Login or Register  to view this content.

+ 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. Copy and paste Excel range as picture into Outlook email body using excel vba
    By ExcelDoc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2016, 09:29 PM
  2. Copy and Paste Union of Ranges/Rows to Outlook Email Body
    By darkhunter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2012, 01:52 PM
  3. Copy paste a table in outlook email to excel
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2012, 04:41 AM
  4. need code to copy text from workbook and paste it in a new outlook message
    By rammergu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-19-2009, 07:36 AM
  5. [SOLVED] Copy- paste from Excel to Outlook Express email
    By JERRY in forum Excel General
    Replies: 1
    Last Post: 04-12-2006, 11:54 PM

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