+ Reply to Thread
Results 1 to 10 of 10

Modify VBA to send email with attachment

  1. 07-04-2012, 06:36 AM #1
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Modify VBA to send email with attachment

    Hi,

    romperstomper wrote this nice piece of code for me which I adjusted slightly.

    Please Login or Register  to view this content.
    Basically it will generate a report for anyone who has a 1 in the VBAtest column of the CaseCounter sheet

    I'm trying to get this to work with emailing now, which he has kindly built-in, but the problem I have is that I want the email nicely formatted too, rather than just 1 line of text, and I don't know how to do this.

    Please Login or Register  to view this content.
    Note the part where it says NAME, that's the associated NAME value on Sheet2 (the same place you draw the CODE, NUMBER and EMAIL from)

    It has to send it to the EMAIL value associated to that same person, for whom the report was generated by the code.

    Thanks in advance
    Attached Files Attached Files
    Last edited by dip11; 07-04-2012 at 08:14 AM.
    Reply With QuoteRegister To Reply
  2. 07-06-2012, 05:20 AM #2
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    b u m p

    I'm guessing it needs to be an adjustment of this:
    http://www.rondebruin.nl/pdf.htm

    Just couldn't get it to work the way I wanted
    Last edited by dip11; 07-06-2012 at 06:26 AM.
    Reply With QuoteRegister To Reply
  3. 07-06-2012, 06:27 AM #3
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    OK I think I made some progress:


    Please Login or Register  to view this content.
    But for some strange reason I now keeps throwing an error "Compile Error: Next without for"

    Considering it used to be happy with the Next without the added email code, what is causing this and how do I fix it?
    Reply With QuoteRegister To Reply
  4. 07-06-2012, 06:32 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Modify VBA to send email with attachment

    You are missing 2 more end ifs before the Next lngRow.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  5. 07-06-2012, 08:48 AM #5
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    Ahhhhh, thanks for that, didn't notice!

    Now this kind of works, except that it brings up the Outlook new message dialog without "To", "Name" and "Attachment"
    So essentially it is not using the EAddress, PName and FileName/FileNamePDF strings to fill those in and send the email :/

    Any ideas?
    Reply With QuoteRegister To Reply
  6. 07-07-2012, 10:17 AM #6
    yogananda.muthaiah
    yogananda.muthaiah is offline
    Forum Contributor
    Join Date
    04-03-2011
    Location
    India
    MS-Off Ver
    Excel 2015
    Posts
    122

    Re: Modify VBA to send email with attachment

    its not converting to pdf ...
    Reply With QuoteRegister To Reply
  7. 07-08-2012, 08:59 AM #7
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    It is converting to pdf just fine - I can see the pdfs being produced in the folder.
    Reply With QuoteRegister To Reply
  8. 07-08-2012, 11:36 AM #8
    yogananda.muthaiah
    yogananda.muthaiah is offline
    Forum Contributor
    Join Date
    04-03-2011
    Location
    India
    MS-Off Ver
    Excel 2015
    Posts
    122

    Re: Modify VBA to send email with attachment

    Can u share the code how the reports are generating in pdf
    Reply With QuoteRegister To Reply
  9. 07-08-2012, 12:53 PM #9
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    Can you be more specific? the code and workbook are all posted, minus the 2 end ifs that arlu spotted, which I have included below.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by dip11; 07-08-2012 at 12:55 PM.
    Reply With QuoteRegister To Reply
  10. 07-09-2012, 05:51 AM #10
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    I've managed to fix it myself.

    Turns out I was using application.match incorrectly and should have used range B:B and jsut mod the number after the comma. And for filename I just needed to add the filepath to the filename...
    Reply With QuoteRegister To Reply
  11. 08-06-2012, 02:27 PM #11
    dip11
    dip11 is offline
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Modify VBA to send email with attachment

    For some reason this doesn't work properly anymore even though I haven't changed anything (other than reboot my PC).

    It seems to use the rng value for PName and generate some random email address (I get bounce backs for failed senders) from my organization.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1