• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

VBA advice

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    VBA advice

    Morning chaps.

    I am trying to automatically send out emails based on a list contained in Excel - I have written the code which will send the email but what I can't work out how to do is send it only when certain conditions are met i.e. when the date shown in the spreadsheet is today's date.

    Spreadsheet format is basic with 5 columns but variable rows - name, email address, text column and then 2 date columns - I need code to send automated email to email address where the date in the corresponding row of the date column is today.

    Hope that makes sense - any advice much appreciated
    Connect with me on LinkedIn

    Follow us on Twitter.

    ContractorUK Best Forum Advisor 2015

    #2
    Could you supply an address to send our invoices to and what are your payment terms?
    'CUK forum personality of 2011 - Winner - Yes really!!!!

    Comment


      #3
      Originally posted by northernladuk View Post
      Could you supply an address to send our invoices to and what are your payment terms?
      Yep certainly can and payment when my problem is fixed So can you fix the problem and how much are you going to rush me NLUK??
      Connect with me on LinkedIn

      Follow us on Twitter.

      ContractorUK Best Forum Advisor 2015

      Comment


        #4
        Where is the email content defined? Is it in Excel as well, or are you trying to read the records from Excel into something else to compose and send the email? How many names are you talking about doing?
        Originally posted by MaryPoppins
        I hadn't really understood this 'pwned' expression until I read DirtyDog's post.

        Comment


          #5
          Excel dates are being stored as a number. With the integer particle is holding the date and the time is being as a decimal, so you must comparing the integer part of date cellular with the integer part of current date/timing. Isn't It?

          Assuming your dates are coming in column 5 (or 'E') and mail addresses column 2 ('B') and the address data most correctfully is in rows 4 to 20, in a worksheet being named 'Data', something like:


          Sub SendSomeMail()

          Const DATECOL = 5
          Const MAILCOL = 2
          Dim Row As Integer
          Dim EmailAddress As String
          Dim DataSheet As Worksheet

          Set DataSheet = Worksheets("Data")
          For Row = 4 To 20
          If Int(DataSheet.Cells(Row, DATECOL).Value) = Int(Now()) Then
          EmailAddress = DataSheet.Cells(Row, MAILCOL).Value
          '' Mail Function here
          End If
          Next 'Row

          End Sub

          Needful done. Payment to Shawaddidi Consulting, Jersey.

          Many thankings.
          Last edited by pjclarke; 24 October 2013, 10:47.
          My subconscious is annoying. It's got a mind of its own.

          Comment


            #6
            Originally posted by DirtyDog View Post
            Where is the email content defined? Is it in Excel as well, or are you trying to read the records from Excel into something else to compose and send the email? How many names are you talking about doing?
            Email address is contained within the spreadsheet - list will be updated regularly - each batch of emails will be around 20-30
            Connect with me on LinkedIn

            Follow us on Twitter.

            ContractorUK Best Forum Advisor 2015

            Comment


              #7
              In which case, if the length of the list may vary, you'll need to add some code to work out the end value for 'Row'.

              PM me for a quote ;-)
              My subconscious is annoying. It's got a mind of its own.

              Comment


                #8
                Originally posted by LisaContractorUmbrella View Post
                Yep certainly can and payment when my problem is fixed So can you fix the problem and how much are you going to rush me NLUK??
                Busted. I don't even know what VBA stands for let alone fix it
                'CUK forum personality of 2011 - Winner - Yes really!!!!

                Comment


                  #9
                  Originally posted by LisaContractorUmbrella View Post
                  Email address is contained within the spreadsheet - list will be updated regularly - each batch of emails will be around 20-30
                  Where is the body of the email - is it also in Excel?

                  If the email contents changes, then I would use Excel to generate a list of the email addresses to send to, separated by a comma, and then copy that into your email program as the CC/BCC list.

                  eg.
                  Col A = email addresses
                  Col B = date
                  Col C = =if(B=TODAY(),A,"")

                  Then have a function to concatenate all the values in C into one field, and copy that list of email addresses into Outlook (or whatever)
                  Originally posted by MaryPoppins
                  I hadn't really understood this 'pwned' expression until I read DirtyDog's post.

                  Comment


                    #10
                    Originally posted by northernladuk View Post
                    Busted. I don't even know what VBA stands for let alone fix it
                    Awww bless ya
                    Connect with me on LinkedIn

                    Follow us on Twitter.

                    ContractorUK Best Forum Advisor 2015

                    Comment

                    Working...
                    X