+ Reply to Thread
Results 1 to 11 of 11

Email from Excel - Hide blank rows before send to show the signature

  1. #1
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Email from Excel - Hide blank rows before send to show the signature

    Dear Gents,

    I am attaching a file which contain a macro to send an email from excel.

    Email body range is B2:G20. Signature on B22.

    i want to modify this code to hide the blank rows and send so the signature can be easily visible.

    It always send all range regardless of blank rows. So i want send the non blank rows only in the email body range.
    Please Login or Register  to view this content.
    Last edited by nasrulla; 01-03-2019 at 07:42 PM. Reason: Remove attachment.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: Email from Excel - Hide blank rows before send to show the signature

    Hi, instead of

    Please Login or Register  to view this content.
    checkuot the use of SpecialCells

    Please Login or Register  to view this content.
    I'm not using my co,puter now so yous google fot SpecialCells there you can see what types there are.

    Another way is to hide the blank rows before , then use SpecialCells for visible rows only and the unhide them after completion.

    Hope this helps and gives you some ideas
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Re: Email from Excel - Hide blank rows before send to show the signature

    Thank you for the quick reply, i will google it for more on SpecialCells

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: Email from Excel - Hide blank rows before send to show the signature

    I modified your code,
    Please Login or Register  to view this content.
    Works but need fine-tuning.
    Happy coding

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: Email from Excel - Hide blank rows before send to show the signature


  6. #6
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Re: Email from Excel - Hide blank rows before send to show the signature

    Thank you Keebellah, when i try to change the range (b20 to b25 and g20 to g25) some how it did not work for me. its start picking adjacent columns data as well.

    I found another similar code i tweak a bit to work. kindly let me know i am making any mistake here,

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: Email from Excel - Hide blank rows before send to show the signature

    If you change from B to G then make sure you edit the vba code too.
    The test is on column B so make that G and set the range accordingly.

    Hard-coding ranges makes it very un-versatile
    I haven't looked at the other code but try correcting the code I sent first

  8. #8
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Re: Email from Excel - Hide blank rows before send to show the signature

    I change to code accordingly. B2:B20 represent the rows and b2:g20 is range we are sending. it is checking non black cells in the range. then with specailcells you select the data to send email.
    if run the code without sending the email, i can see it is hiding black rows and selecting correctly.

    while sending email it pick the next column data. very strange.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: Email from Excel - Hide blank rows before send to show the signature

    No it's not, it's because you're using the envelope method which send the selected worksheet
    You should add code to hide all not wanted columns

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: Email from Excel - Hide blank rows before send to show the signature

    I made some modifications
    Fill the to and cc emails in the yellow cells in sheet2
    go to sheet1 and select the range you want to mail and rightclick
    Let me know
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-25-2017
    Location
    Dubai
    MS-Off Ver
    365 ProPlus
    Posts
    42

    Re: Email from Excel - Hide blank rows before send to show the signature

    Thank you.

+ 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. [SOLVED] send email using vba with default image signature and a code error
    By gotid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-11-2018, 10:07 AM
  2. VBA to hide/show rows, if column B is blank
    By LJenkins37 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2017, 09:36 AM
  3. Trouble adding a signature to a VBA coding to send an email
    By chrisellis250 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2017, 05:02 AM
  4. Use VBA to send email from Outlook and use senders default signature
    By BillDoor in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-21-2015, 11:02 AM
  5. Macro to send email with signature depending on cell content
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-12-2013, 11:39 AM
  6. send selected range in email with default outlook email signature included
    By mdsickler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2013, 10:50 PM
  7. Function/VBA or something to show hide signature(image) using a pin number.
    By smarcins in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2012, 05:39 AM

Tags for this Thread

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