1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved E-mail reminders from almighty Excel to Outlook

Discussion in 'Business Applications' started by Georgianthepadawan, May 9, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. Georgianthepadawan

    Georgianthepadawan Thread Starter

    Joined:
    May 7, 2016
    Messages:
    5
    Hi guys,

    I have read tons of pages from this forum hoping to create a killer excel workbook so my daily tasks can be completed way faster and more efficient, however I have hit a brick wall. That brick wall is called "How in (insert whichever holy character you want)'s name do I get Outlook to obey Excel".

    My request is rather simple, yet I don't have the brains for it. Once a week, probably on Monday, based on the attached excel, I want to send specific e-mails to colleagues informing them which documents should they start writing based on the "Deadline to be filled" column and to also inform them what is going to happen based on the Column "When will it happen". Here's how I think the code should be displayed in my humble and unadvised opinion:

    If "Col G" = yes, then do nothing
    if "col C" is between today() and today()+7, then e-mail must be displayed


    Subject = Work schedule
    To: “Col E”
    Cc: “Col F”

    Body:
    ‘1ST PART - I am trying my best to understand excel/vba language, so I'm adding hypens like y'all pros when I do a comment :D

    Dear colleagues,

    The following actions must be completed this week:
    - “Col A” for “Col B” – deadline to be filled “Col C”
    - “Col A” for “Col B” – deadline to be filled “Col C”
    - Preliminary hearing for Yellow SRL – 09.05.2016 (example)

    ‘2ND PART

    Also, if "col D" is between today() and today()+7, then the following text must also be contained in the same e-mail

    Also this week the following will happen:
    - “Col A” for “Col B” – “Col D”
    - “Col A” for “Col B” – “Col D”
    - Preliminary hearing for Green SRL – 12.05.2016 (example)

    Side notes:

    - If the second part can’t be contained in the same e-mail, the code for the first part will be more than enough (I will try my best to replicate it for a distinct e-mail regarding the second one after you wizards show me the way).

    - There is no need for "Col G" to be completed as a send report, I will manually keep track on it since there are two parts of each row that can be contained in two different e-mails.

    - I want this excel only to trigger the .display command when I open it and will enable macros (I will only send these emails once on a week, every Monday)


    How this week’s e-mail should look like:

    Dear colleagues,

    The following actions must be completed this week:
    Bank deposit for Orange SRL – deadline to be filled 04.05.2016
    Preliminary hearing for Green SRL – deadline to be filled 05.05.2016
    Declarations for Yellow SRL – deadline to be filled 08.05.2016
    Notification For White SRL – deadline to be filled 09.05.2016
    Preliminary hearing for Yellow SRL – deadline to be filled 09.05.2016


    Also this week the following will happen:
    Bank deposit for Orange SRL – 11.05.2016
    Preliminary hearing for Green SRL – 12.05.2016
    Declarations for Yellow SRL – 15.05.2016


    As you can see from the attached excel, I'm an A+ excel newbie myself (I actually have a code working for the dropdown list to hide/unhide other rows, a code about which I'm uber-satisfied and I'm using it 24/7), but for this e-mail matter I have tried editing so many of your codes from here or there and I don't seem to get a hold on some key elements like how do you correspond the cells in code with sintaxes like Bcell.value(0, -6), that's just rocket science for me.

    So, to be straight, I'd rather understand how it can get done rather than having it handed to me on a silver platter. I'm already consuming myself a bit over it (I'm working on it even on weekends, off the clock), since I have recently found out I have a secret passion for this little monster called VBA. (it's litteraly mind-blowing, the more I'm finding out, the more I'm realising I don't know sh*t just yet).


    Also, I feel kinda weird asking for free advice as a newcomer on a forum that is clearly visited by people way more qualified than me, so I can only pay my tribute with a song - a new one from Radiohead.
     

    Attached Files:

  2. Georgianthepadawan

    Georgianthepadawan Thread Starter

    Joined:
    May 7, 2016
    Messages:
    5
    Hi guys once again, I have made some adjustments to the excel WB so it can be easier to complete the code:
    - there will always be the same iTo and same iCc, so they will be identified within the same cell every single time.
    - I made two IF columns so the e-mail should be displayed wheter those columns contain "This week" or not.

    So, to summarise it again, I would like the following to happen:


    If Col E = "This Week", then

    iTo = B2
    iCc = C2
    iSubject = "This week's schedule"
    iBody =

    Dear colleagues,

    The following actions must be completed this week:
    - “Col A” for “Col B” – deadline to be filled “Col C”
    - “Col A” for “Col B” – deadline to be filled “Col C”

    Also this week the following will happen: (if Col F = "This Week")
    - “Col A” for “Col B” – “Col D”
    - “Col A” for “Col B” – “Col D”

    .display

    I think that's it. I'm actually so close to developing it on my own, but I'm really swamped. Gracias amigos!
     

    Attached Files:

  3. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Try the attached, I have formatted the body as HTML so that I could bold the line

    The workbook will generate the email when the it is open
     

    Attached Files:

  4. Georgianthepadawan

    Georgianthepadawan Thread Starter

    Joined:
    May 7, 2016
    Messages:
    5
    Hello mate,

    Thanks for the prompt response and also for the awesomeness of the xlsm you've just uploaded. I stole a bit of your mojo and I've tweaked it a bit myself with some <br>s, added some text before 2nd body and some other minor stuff and it looks awesome now.

    Nevertheless, I got two questions -

    Uno. when you write cell.offset(0, -1), the cell indicated is the first one on the left of the Range? How would the first one on the right look like, .offset(0, 1) or .onset(0, -1)? I've read about this and usually in VBA I saw formats like (x, y) where x indicates the Row, and Y the column, so the cell (1, 2) should be B1.

    Dos. Can I format it from VBA to have a specific font and size? Should it look like iBody.Font.Name = "Calibri" and iBody.Font.Size = 11? And if so, where do I add these lines?

    Thank you so much for your "wizardness",
    Georgian
     
  5. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    75,360
    Georgianthepadawan, I've deleted your entire poll as it contained vulgarity and offered nothing to the thread. Please remember that this is a family oriented site.

    thanks,

    v
     
  6. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Georgianthepadawan,

    Echoing valis's post above, please remember this is a family forum so refrain from using profanity or vulgar language. it is not necessary especially when you are looking for help.

    I have updated the code so that the email body is formatted to size 11 and font Calibri

    as for the offset question.

    if you look at the code you will see that everything begins with Column E, range E5 to be exact

    I loop through each cell in this column and therefore am able to use this reference point to refer to cells adjacent to it, left or right

    So if I start in E5 and want to refer to D5, I can say something like

    Code:
    .range("E5").Offset(0,-1)
    Minus will go to the left of the range and a positive number will go to the right

    x and y can also be used but this has to be used within a loop so that you increment both row and column as needed.

    Code:
    Sub GetValues()
       
        Dim iCol As Long
        Dim iRow As Long
       
        iCol = 1
        iRow = 5
       
        For i = 1 To 10
            If Cells(i, iCol) <> Empty Then
                Debug.Print Cells(i, iCol)
            Else
                iCol = iCol + 1
            End If
        Next i
       
    End Sub
    So if you take the above as an example, what this is doing is looping through the cells in column 1, then as soon as it finds an empty cell, it will increment the column by one and then capture the data in those cells until it finds a blank...
     

    Attached Files:

  7. Georgianthepadawan

    Georgianthepadawan Thread Starter

    Joined:
    May 7, 2016
    Messages:
    5
    Sorry for the poll, I had no intention of offending anyone. It has been dully noted and it shall never occur again.

    Thank you for explaining it to me in such a simple manner, Database, I have finally understood the use of cell.offset and I can now tweak the code myself however I desire. From now on, sky is the limit.

    With your permission, I would like to scratch this thread as being solved, since you have provided all the answers I was hoping for.
     
  8. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Absolutely, please mark as solved if your requirements have been met, and please do come back if you ever need anything else.

    thanks for coming back with the results.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1171045

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice