+ Reply to Thread
Results 1 to 12 of 12

Creating a button that when clicked refreshes a cell (API Pull) and then sends an email

  1. #1
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Creating a button that when clicked refreshes a cell (API Pull) and then sends an email

    Hi,

    GOAL: Create a button that when pressed it refreshes an API (This can simply be a refresh of a cell - I'm using third party software to pull the data in so the cell simply needs to refresh and it'll pull in live data), and then once the data has refreshed it'll create an email of the designated area (pre set) and send the email to a list of specified contacts.

    I'm not even sure if this is all possible at all - worth asking tho!

    I've attached a sheet with mock info.

    1. 'API Sheet' - the cell highlighted in yellow is the cell that needs to be refreshed
    2. 'INFO' - the area that needs to be emailed is boarded and highlighted in yellow
    3. 'INFO' - the area highlighted in red is where the button should go

    Thanks so much in advance.

    I don't even know where to start - I guess that's why these platforms are here haha!

    Regards,
    Zohan
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Concenttrate on getting the API updating. The rest is easy.

    Play with something like:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 11-09-2021 at 10:43 AM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Thanks so much for helping. I can't thank you enough.

    The VBA code has had an error - when I click debug it returns the below (picture linked)
    Attachment 754781

    Please if you get a chance could you help again - having this working will be a god send!

    Let me know.
    Z

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    I cannot see your attachement.

  5. #5
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Plus if you'd be able to share how I send the email out once the API has refreshed that'd be a god send haha!

  6. #6
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Here you go - https://ibb.co/zQPRTfM

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Try

    Please Login or Register  to view this content.

    This is the Email Code that I have used before.

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-09-2021 at 07:36 PM.

  8. #8
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    So when i use the formula listed above it replaces the API formula with 'target' - which then won't pull the data.

    The formula that's in C8 is =cs.querya(b3,b4) - (this is the cell that's being refreshed when I click the button) - with the current vba code it replaces that formula with 'target' - and therefore doesn't pull any data.

    Is there a fix?

    Thanks so much for the input.

  9. #9
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    so , I've got everything working other than the email being sent from the correct address.

    I'm using this code:
    Sub sendEmail()
    Dim EmailApp As Outlook.Application
    Set EmailApp = New Outlook.Application

    Dim EmailItem As Outlook.MailItem
    Set EmailItem = EmailApp.CreateItem(olMailItem)

    EmailItem.SentOnBehalfOfName = "[email protected]"
    EmailItem.To = "[email protected]"


    EmailItem.Subject = "Test Email From Excel VBA"

    EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel"

    EmailItem.Send

    End Sub


    So how do I get it to be sent FROM the address listed above - even with the code it doesn't get sent from [email protected] - nothing happens when I execute this code.

    When I remove the EmailItem.SentOnBehalfOfName = "[email protected]" it gets sent to the correct address but is from another address I have in outlook.

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    You are having more success than I have.

    My Outlook will not work at all.

    I'll get back to you if I can get it working.

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-10-2021 at 10:59 AM.

  11. #11
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Thanks. It seems to be working on Outlook accounts i.e non gmail accounts. But as soon as I try and send it from my gmail account it goes dead.

  12. #12
    Registered User
    Join Date
    09-14-2021
    Location
    England
    MS-Off Ver
    MS365
    Posts
    68

    Re: Creating a button that when clicked refreshes a cell (API Pull) and then sends an emai

    Sub sendEmail()
    Application.OnTime Now + TimeValue("01:00"), "sendEmail"
    Dim EmailApp As Outlook.Application
    Set EmailApp = New Outlook.Application

    Dim EmailItem As Outlook.MailItem
    Set EmailItem = EmailApp.CreateItem(olMailItem)

    Dim rng As Range

    EmailItem.To = "[email protected]"
    EmailItem.CC = "[email protected]"

    EmailItem.Subject = "Update"

    EmailItem.HTMLBody = "Hi," & "<br>" & "<br>" & _
    "Please see the below:"


    Set rng = Sheets("Sheet2").Range("b2:f22").SpecialCells(xlcelltypevisable)

    EmailItem.HTMLBody = RangeToHtml(rng)



    EmailItem.Send

    End Sub

    In this code what have I done wrong - sending a test email before worked but now I'm trying to pull cells as part of the email it refuses to work. Please can you let me know what I need to add to get this working?

    Thanks again mate

+ 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] copy and past specific cells to body of email when option button is clicked
    By steve400243 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-27-2017, 05:02 PM
  2. Im looking to create a button that sends email
    By fragle2112 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2016, 10:08 AM
  3. [SOLVED] Pull a Hidden frame when clicked command button.
    By venkcris in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-02-2014, 08:52 PM
  4. Run-time error 70 when button is clicked to run macro that opens email
    By blacklotus0014 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-02-2013, 09:54 AM
  5. Replies: 0
    Last Post: 03-14-2013, 01:27 AM
  6. Command Button that sends email
    By msbing916 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2009, 12:29 PM

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