Results 1 to 8 of 8

Thread: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse practices

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    22

    Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse practices

    Hi everyone, I thought I would give this forum a second go. Even if I answer my own questions again, hopefully it might help someone else. I work mostly in computer vision, and thus my language experience didn't include VB until about month ago (though I think I'm learning fast), when I was co-opted to help my lab move.

    I was wondering what the best practices/plan/logic flow would be for the following problem:
    1. taking data that is in SQLServer with multiple tables, do a one time migration to an Excel Spreadsheet [I know this is soo backwards]
    2. create a daily form sheet (in Excel) where a user can input several manual readings (thinking of these as global variables) [coming from analog readings...I may try to automate that later]
    3. create a user-specific report based the newly entered global values and user-based variables/constants [currently about 60 users, user variables include things like distance multipliers]
    4. send all the users their user specific email, preferably in the body of the email (I just got some of these guys off faxes, don't want to teach them to read a .csv file)


    My first thought when given this project was to handle it all in SQL (where I'm comfortable), or do a port/C++ ODBC . Boss man wants it all in Excel, in an macro. I don't feel like that's the best way to do this, but he seems to hate updating the original database, and so he just want to "put everything in Excel and push a button." In case you are wondering, not only did I not design the original database, I haven't even used it before today.

    Going into my first thoughts as I try map out a plan:
    I was going to put each of the tables [users, locations, etc] into it's own sheet. Would it be better if it was it's own file? Then I will make another sheet for the for the daily readings to be plugged in. And finally have a button to run a monstrously large (perhaps I don't have perspective yet) macro.

    Where would the best place to store each user report be? Make an CSV file? Keep it in memory until it's sent to Outlook then dump it in a loop? Boss man uses Outlook, but I may not have to use it. Assuming that I do end up using Outlook, if I use .send in a loop, he won't actually have to click send or anything to have the reports mailed to the users? That was my understanding, but most of the examples I saw included a .display line (which I assume I can just leave out).

    Thanks in advance for your input, and cheers to you all who do this all year long. I'd hate it.

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    If you are happy in SQL... so stick it in there.
    The top man is not comfortable updating main data - don't. Drop all the users bits into a separate table. You can then run validation etc on them before commiting to the db.

    Initially sounds like a data capture and entry to db. Will the users need to see anything? Will the users need to update existing data or just add new ?

    Can you not connect Excel direct to the SQL in the connection string? So the user sees only a data capture and click a button, but behind the scenes it goes to your fave environment; SQL server.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    Quote Originally Posted by Volos View Post
    Boss man uses Outlook, but I may not have to use it.
    Assuming that I do end up using Outlook, if I use .send in a loop, he won't actually have to click send or anything to have the reports mailed to the users?
    He still will need to click, but it's just a warning that says something is trying to send an email on your behalf, do you want to...?

    .Display only generates the email onto the screen for the user to view before they send.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    22

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    Quote Originally Posted by Ecniv View Post
    If you are happy in SQL... so stick it in there.
    The top man is not comfortable updating main data - don't. Drop all the users bits into a separate table. You can then run validation etc on them before commiting to the db.

    Initially sounds like a data capture and entry to db. Will the users need to see anything? Will the users need to update existing data or just add new ?

    Can you not connect Excel direct to the SQL in the connection string? So the user sees only a data capture and click a button, but behind the scenes it goes to your fave environment; SQL server.
    Good to know others have the same initial thoughts I did. There are some unfortunate problems. First the poor old server the DB is on is slated to be junked, and a copy of the DB is to be thrown on optical media then put in a box where it never sees the light of day (unless there's a problem). I realize I could run the DB locally, but the boss man (let's call him BM) wants everything in Excel. For lack of a better word, I don't think he ever "trusted" the data in DB as he felt he couldn't directly change and monitor it. This despite the fact the PHP front end seems to be operating correctly. I tried to point out it had much greater integrity in SQLServer (or even ported to MySQL which is what I really want to do), but I got shut down. Perhaps I should take some negotiating classes.

    Any way so the spec and scope is in VB, so I'm at least going to try and cobble it together from Excel.

    The users table is a bit misleadingly named. It would be better named "field_agents," as the people are never connecting to the database (which was only locally available anyway). In fact, BM and one other person have been handling everything on the DB as sa (Skrik moment now). Really the 'users' table is only relevant to the future of the project in so far as it contains their contact information, and a series of user-specific base values to be used in some logic.


    Quote Originally Posted by Ecniv View Post
    He still will need to click, but it's just a warning that says something is trying to send an email on your behalf, do you want to...?

    .Display only generates the email onto the screen for the user to view before they send.
    Right so on this, if I put generating the email in a loop, he will only have to do this once as a security measure, or does each message need to be confirmed. My first simple test this morning would indicate that it only has to happen once (two blank emails to myself).

  5. #5
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    I think the email (MS Outlook) asks once and allows vba code for ten minutes... after that it will ask again.


    So essentially the fairly decent (if old) solution is slated to be removed and the BM wants it all in excel cause he can change as required...

    Option to use Access?
    At least you can keep a relational database using it. Maybe a VB front end..?

    I'm not comfortable with the
    "trusted" the data in DB as he felt he couldn't directly change and monitor it.
    that statement could be very worrying


    I hope you find a solution that works for you n BM

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    22

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    Access might be a viable option, but at the moment I'm thinking an entirely VB front end that prompts for the daily values and pulls from csv files for client data. Is there any reason that I would have to store each individual report somewhere, or could the emails efficiently be created that sent the values of 40 or so variables (created from global+field_agent) that are recalculated and sent on a loop?

    For one report I currently have 75 calculations, 4 Gets, and then however I'm going to email. I'm worried on the time over a 60 iteration loop. I guess I will start benching the time by spamming myself hardcore.

    I hope to have a semi-working code up by tomorrow night.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    22

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    Ok so I'm moving along in hacking this together in VBA, and I have actually imported the whole DB into one Excel file (turned out to have 12 tables, all of which I put on a separate sheet). I've already groaned enough for the lot of us on that regard. As I've mentioned I'm new to VBA, and I'm noticing there's no skip/continue command. The records from the DB aren't perfect, so if I don't have the email for the contact, I don't want to take the processing time to draft an email I can't send. The work around I have is nesting the whole operation in an intial if statement:
    Code:
    Sub Terminal_Arranger()
     Dim terminal_count As Long
     Dim terminal_list As String
     Dim i, count, x, lastRow As Long
     Dim SpData As Worksheet
     On Error GoTo Err 
     Set SpData = Sheets("User-data")
     lastRow = SpData.Range("A" & Rows.count).End(xlUp).Row
     SpData.Select
     For i = 2 To lastRow
       If IsEmpty(Cells(i, 29)) Then
        Cells(i, 32).Value = "No Email"
       Else
         ///rest of my program
       End If
     Next i
     Exit Sub
    
    Err:
       ///error handling
    
    End Sub
    Is there a better or faster way to handle this? I don't want to exit to my Error handling for the scattered invalid enteries I have. I am still cranking through the data validation and repair (I'm cheating and using python for that), so this may not be an issue in the future, but I would like to know the way most people handle it.

    Cheers,
    Volos

  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse pract

    Either that or get the length of the cells value

    Code:
    if len(sht.cells(1,1))=0 then 
    'there was nothing in the cell
    else
    'there was something in the cell
    end if

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width