none
VBA automatic email based on cell reference RRS feed

  • Question

  • Hi

    I am trying to set up a automatic email from a excel spreadsheet based on a cell reference, saying "service due" however the body of the text needs to include another cell with the trailer number, so it says "Service Due 70" or something like that.

    I have put the formulas in to work out the service due according to date on the sheet.

    the only trouble is I am a bit of a novice to VBA and cant make it work.

    can anybody help please with the correct coding.

    here is the top two lines of my sheet with headers but there are over 70 trailers I need to include.

    Vehicle   ID Mark VIN Nr. AA Trailer ID 11-12-15
    C300279 RT10362010 98 Service Due 01-02-16

    Friday, December 11, 2015 8:09 PM

Answers

All replies

  • Hi warden8632,

    According to your description, I don’t understand your requirement.

    >> so it says "Service Due 70" or something like that.

    I just find Service Due in the AA Trailer ID column, what’s 70 mean?

    >> I have put the formulas in to work out the service due according to date on the sheet.

    How do you achieve that?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 14, 2015 2:44 AM
    Moderator
  • Hi Sorry I didn't explain it well, 70 or the number next to service due is the trailer number that needs a service, in the sample I gave the trailer number is 98, but the sheet contains 70 trailers, so I need to incorporate the trailer number in the email somehow, if that is possible, sorry for the confusion. Regards Warden

    Below is the formula I have used on the sheet to work out when a service is due, according to todays date

    I would upload the sheet but I cant find out how to do this,

    =IF(E3>(D1+30),"Service   Due",)


    • Edited by warden8632 Monday, December 14, 2015 7:07 AM
    Monday, December 14, 2015 6:30 AM
  • Just concatenate the id to the 'Service Due' string:

    =IF(E3>(D1+30),"Service   Due" & CellWithTrailierID,"answer if false")

    Monday, December 14, 2015 4:58 PM
  • Hi Dogubob Thanks for that the problem is I don't know how to write the code for the automatic email to include these cells and then repeat it for all 70 trailers. Regards Warden
    Monday, December 14, 2015 5:22 PM
  • Hi warden8632,

    You could get last row and column by using Range.End property, then iterate rows and get each cell value by using Range.OffSet property.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 16, 2015 3:07 AM
    Moderator
  • Please check this link.

    http://www.rondebruin.nl/win/s1/outlook/bmail9.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, December 21, 2015 3:40 PM