+ Reply to Thread
Results 1 to 10 of 10

Send a outlook email through the excel sheet

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016,office 365
    Posts
    69

    Send a outlook email through the excel sheet

    Dear Experts,
    I need to send a email on daily basis through the excel sheet in the attached format.I made some codes, its not working.Anyone please review and amend my code.
    If column K = yes, then I need send a mail with attachment.
    To address in Column G
    CC in Column H
    Subject in Column I and
    attachment path in column J
    once email was send and i need the sending date in column L
    Attached Files Attached Files

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

    Re: Send a outlook email through the excel sheet

    You can't send a cell as a attachment. It has the be at least a sheet. We can copy the cell to a new sheet and send that.

    Or put the cell in the body of the email?

    Or export the range to a text file?
    David
    (*) Reputation points appreciated.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Send a outlook email through the excel sheet

    The loop starts by looking in A2 for an email address but that cell is blank, and that column doesn't contain email addresses anyway. It needs to start in G3.

    The syntax to retrieve the value from N2 is incorrect.

    These two problems are fixed in the attached.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Send a outlook email through the excel sheet

    Quote Originally Posted by Tinbendr View Post
    You can't send a cell as a attachment.
    Good point, I overlooked this. I focused on just getting the code to do something. I did not look at the attachment issue. Please explain what you want to attach.

  5. #5
    Registered User
    Join Date
    08-30-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016,office 365
    Posts
    69

    Re: Send a outlook email through the excel sheet

    Thank you so much Mr.6StringJazzer,
    Now its works fine, but once email was sent out I need update in L column,ie sent on date(xxxxx),Can you help me once again

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Send a outlook email through the excel sheet

    Immediately after the End With, add this line of code:

    Please Login or Register  to view this content.
    Also, regarding Tinbendr's comment, you are creating the attachment correctly. You are using the path name from a cell to add an attachment (not literally attaching the cell), which works just fine the way you are using it.

    I noticed another issue. You need to change "a1" to "A2" everywhere in your code.

    I do not advise the way you are using Offset to refer to everything, but I was trying to make the minimal changes in your code to get it to work. It works, but an example of a more readable, maintainable way to do this would be to change this

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    You also use the variable emailaddress to capture an address then always assign it to mainemail, and mainemail never has a value except what is assigned from emailaddress. So you don't need both.

    Here is how I would rewrite your code:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-30-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016,office 365
    Posts
    69

    Re: Send a outlook email through the excel sheet

    Dear Mr.6StringJazzer,
    Thx for your reply, I am facing the problem in attachment, its not picking the file and huge time consumed for processing , its calculating each line every time.Could you please help me to resolve this issue..

    Once again Thanks in advance
    Rgds
    krishnakumar

  8. #8
    Registered User
    Join Date
    08-30-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016,office 365
    Posts
    69

    Re: Send a outlook email through the excel sheet

    Dear Mr.6StringJazzer,
    Sorry to disturb you, Could you please help me on above said issue,because I am using more than 1000 lines that sheet.The attachment problem is resolved...
    Once again Thanks in advance
    Rgds
    krishnakumar

  9. #9
    Registered User
    Join Date
    08-30-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016,office 365
    Posts
    69

    Re: Send a outlook email through the excel sheet

    Dear Experts,
    Still I am facing the problem (processing time is huge), Could please help on my code
    Attached Files Attached Files

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Send a outlook email through the excel sheet

    I suspect part of the performance issue is that you are doing VLOOKUP on an external file. I do not know why it would be recalculating, but it may speed things up to open that external file before you run your macro. The performance problem is not in the macro code.

+ 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. convert excel sheet to pdf using macro and send that pdf via email using outlook
    By suresh mongam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2015, 08:51 AM
  2. [SOLVED] Send email from Excel using Outlook
    By clapforthewolfman in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-10-2013, 05:19 PM
  3. Replies: 1
    Last Post: 10-03-2012, 07:31 AM
  4. Send email from Excel to Outlook
    By shaukat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-02-2011, 04:46 PM
  5. Send an email with excel VBA through outlook
    By Alicita in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-10-2011, 05:03 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