Advise on using multiple instances of network files based on customers

Status
Not open for further replies.

reubendayal

Senior Member
Outlook version
Outlook 365 64 bit
Email Account
Office 365 Exchange
Hi All,

I have a macro we use to copy several document templates based on the company we are assisting. So for now it has worked just fine by using several IF statements based on each company. Here's an example:

Code:
If Employer = "Apple" Then

    SFolder = "S:\Network Location\Company Folders\Cases\"
    EmployerTempLink = "S:\Network Location\Company Folders\Cases\Templates\"
    EmployerQstName = "Questionnaire.xlsx"
    EmployerDecName = "Declaration.pdf"
    POAlink = "S:\Network Location\Company Folders\Cases\"
    POAName = "Letter.pdf"
    Apple = True

ElseIf Employer = "Orange" Then
… (continues like this for 15 different companies)

Now we have 20 more companies to add that just makes this code that much more longer with this approach.

Secondly, every time a team member changes the file location/name of the network folders even slightly it breaks the code.

So I've been wondering if I must simply put these network address in a common excel sheet so the team members can keep that updated if there are any modifications required. Also, it makes the updating of names less complicated for non-programmers. The obvious fact remains that if anyone mistakenly deletes the excel file or changes things in an incorrect manner, then the code breaks again.

I've also thought that perhaps the above code can be made in to a function to prevent the same lines of code being repeated each time. But I do not have the skills to put that together.

Any advice will be much appreciated!

Thank you.
 
Hi All,

I have a macro we use to copy several document templates based on the company we are assisting. So for now it has worked just fine by using several IF statements based on each company. Here's an example:

Code:
If Employer = "Apple" Then

    SFolder = "S:\Network Location\Company Folders\Cases\"
    EmployerTempLink = "S:\Network Location\Company Folders\Cases\Templates\"
    EmployerQstName = "Questionnaire.xlsx"
    EmployerDecName = "Declaration.pdf"
    POAlink = "S:\Network Location\Company Folders\Cases\"
    POAName = "Letter.pdf"
    Apple = True

ElseIf Employer = "Orange" Then
… (continues like this for 15 different companies)

Now we have 20 more companies to add that just makes this code that much more longer with this approach.

Secondly, every time a team member changes the file location/name of the network folders even slightly it breaks the code.

So I've been wondering if I must simply put these network address in a common excel sheet so the team members can keep that updated if there are any modifications required. Also, it makes the updating of names less complicated for non-programmers. The obvious fact remains that if anyone mistakenly deletes the excel file or changes things in an incorrect manner, then the code breaks again.

I've also thought that perhaps the above code can be made in to a function to prevent the same lines of code being repeated each time. But I do not have the skills to put that together.

Any advice will be much appreciated!

Thank you.

Here's my idea on how I would use the excel sheet to store all the company and documents related location. First based on the 'Employer' name we receive via a user form, find corresponding Company name in the excel sheet. Let us say the column 'B' in the name of the employers (just as they are written in the network drive). Then in each corresponding column within the same row, provide the location data for templates - EmployerQstName, EmployerDecName, POAlink and POAName.

I hope this gives you a better picture of what I am trying to achieve.

thanks again
 
Hi All,

I have a macro we use to copy several document templates based on the company we are assisting. So for now it has worked just fine by using several IF statements based on each company. Here's an example:

Code:
If Employer = "Apple" Then

    SFolder = "S:\Network Location\Company Folders\Cases\"
    EmployerTempLink = "S:\Network Location\Company Folders\Cases\Templates\"
    EmployerQstName = "Questionnaire.xlsx"
    EmployerDecName = "Declaration.pdf"
    POAlink = "S:\Network Location\Company Folders\Cases\"
    POAName = "Letter.pdf"
    Apple = True

ElseIf Employer = "Orange" Then
… (continues like this for 15 different companies)

Now we have 20 more companies to add that just makes this code that much more longer with this approach.

Secondly, every time a team member changes the file location/name of the network folders even slightly it breaks the code.

So I've been wondering if I must simply put these network address in a common excel sheet so the team members can keep that updated if there are any modifications required. Also, it makes the updating of names less complicated for non-programmers. The obvious fact remains that if anyone mistakenly deletes the excel file or changes things in an incorrect manner, then the code breaks again.

I've also thought that perhaps the above code can be made in to a function to prevent the same lines of code being repeated each time. But I do not have the skills to put that together.

Any advice will be much appreciated!

Thank you.
Seems to me you should be using a database management solution.
 
Seems to me you should be using a database management solution.
Thanks AlphonseG.

You're right this should rather be in a database management solution. But firstly the team I am working with are have very limited understanding and courage towards trying anything tech. Besides, I do not have the time and perhaps all required skills to rebuild the entire code in to a database management solution.

Therefore, solving the above issues within this code is what I am going with. So any further help on that will be highly appreciated.
 
Of course, it is a business decision as to where your time is best spent. Whether it is spent performing the tasks that you are good at, or trying to patch something that should be done by someone with the proper skills and training. I have a client (over 20 years now) who had taken a couple of classes in MS Access. He decided that he could make more money running his business, than he would save by not using a professional developer.
 
Of course, it is a business decision as to where your time is best spent. Whether it is spent performing the tasks that you are good at, or trying to patch something that should be done by someone with the proper skills and training. I have a client (over 20 years now) who had taken a couple of classes in MS Access. He decided that he could make more money running his business, than he would save by not using a professional developer.
Dear AphonseG,

Honestly none of your responses have been of any help. If you’re frustrated with your customer situation it is unfair to be slamming others with such a prejudice!

Isnt it the idea of such forums to be a place where people help each other out as some know more about the subject than others?
 
Dear AphonseG,

Honestly none of your responses have been of any help. If you’re frustrated with your customer situation it is unfair to be slamming others with such a prejudice!

Isnt it the idea of such forums to be a place where people help each other out as some know more about the subject than others?
Don't know where you are getting any idea of frustration, slamming or prejudice, as I expressed neither. Guess you didn't quite understand my point. I'm merely suggesting that you might be better off in the long run by going in a different direction.
 
You're putting these values in variables, so it should be as condensed as possible (or very close) - the code that uses these variables can be in a function or separate macro or just lower in the macro.

If you start the code in outlook, you can't avoid a ton of If's - one per company, but you could convert it to an array - if the variables are in a CSV or they are filed by company name.

If you start from a contact or task, the values could be in custom fields or the body and the macro reads them.

If you start from a spreadsheet, you can read the values in the row.

I would tell people "Do not move files".... and if the POAlink is not always in the cases folder, move it in. If you need different files for each company, I would arrange by company name - either folders or filename have the company name. Then you can use this format -
EmployerQstName = Employer & "_Questionnaire.xlsx"
or
EmployerQstName = SFolder & Employer & "\Questionnaire.xlsx"

If sfolder is always the same - set that before the If starts so you only need to set it once. Also easier to update if the location changes.

If Employer = "Apple" Then
SFolder = "S:\Network Location\Company Folders\Cases\"
EmployerTempLink = SFolder & "Templates\" - always in sfolder?
EmployerQstName = "Questionnaire.xlsx"
EmployerDecName = "Declaration.pdf"
POAlink = "S:\Network Location\Company Folders\Cases\" - always in sFolder?
POAName = "Letter.pdf"
Apple = True
Goto codetofinish ' if using a function or separate macro, call it here
else if....
' next company
end if


codetofinish:
' do whatever with the variables
 
Thank you, Diane.

This is just amazing as I was thinking myself on the same lines of code:
Code:
EmployerQstName = Employer & "_Questionnaire.xlsx"
or
EmployerQstName = SFolder & Employer & "\Questionnaire.xlsx"

I was also planning on using the fixed If sfolder location. And also fix the locations for each company's template documents in the company's subfolder - for example - "S:\Network Location\Company Folders\" & "Employer Documents"

I had'nt thought or even know it was possible to use a contact or task, where the values could be in custom fields or the body and the macro reads them.

I've also told the team on several occasions to not rename/move/mess with the folder structure on the network drive. But a slip happens from time to time.

The key aspect is that the macro must start in Outlook. As it is an initiation email from the customer I use which is currently selected (or a selection of emails), and pull data from it and partly from a userform completed by the team member at the start of the macro, to create a new case. During this process the macro creates network and outlook folders for the case, copies over templates to the network folder after renaming them, and moves the email selection to the case folder, enters details in an access databased and one or more excel sheets based on the customer account, etc. Therefore, for the team it is easiest to use the macro from within Outlook.

One additional aim from creating the excel sheet is that I am trying to make it smart by adding a macro to grab the customer account path and outlook folder information in to the sheet. This will also counter the issue of the team changing a folder name. And that way it can be possible to find all relevant details in the same row of the customer. For example, from the company name, network folder, outlook folder, Invoicing, sheet name or location on the network folders, etc.

I am unsure of the code on finding the row reference in the excel that has the matching Company name in the Company column and select it. As then I should be able to use the target offset function to grab the other corresponding information on the company.

I hope I've described my intent with the excel sheet approach.

Thank you.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
C Outlook 365 Microsoft Support - Please Advise Using Outlook 3
N Please advise code received new mail Using Outlook 0
R Which event should i advise? Outlook VBA and Custom Forms 2
S Custom appointment form NEED ADVISE Outlook VBA and Custom Forms 1
H using VBA to edit subject line Outlook VBA and Custom Forms 0
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 10
e_a_g_l_e_p_i Outlook 2021 not letting me setup my Gmail using pop Using Outlook 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
O How to find out the domain and server settings that my Outlook is using? Using Outlook 2
S Outlook 2019 Custom outlook Add-in using Visual Studio Outlook VBA and Custom Forms 0
D Outlook 2021 Using vba code to delete all my spamfolders not only the default one. Outlook VBA and Custom Forms 0
M using excel to sort outlook appointment items Outlook VBA and Custom Forms 4
HarvMan Using Emojis in Outlook 365 Using Outlook 3
T Outlook 2019 Not Using Auto Compete After Deletion of 365 Using Outlook 1
M USING INITIALS AS RECIPIENTS Using Outlook 1
T Outlook 2019 Using Gmail aliases in Outlook Using Outlook 6
M Saving emails using Visual Basic - Selecting folder with msoFileDialogFolderPicker Outlook VBA and Custom Forms 6
Z Import Tasks from Access Using VBA including User Defined Fields Outlook VBA and Custom Forms 0
justicefriends How to set a flag to follow up using VBA - for addressee in TO field Outlook VBA and Custom Forms 11
M Extract "Date sent" from emails (saved to folder using drag and drop) Outlook VBA and Custom Forms 1
I Outlook for Mac 2019 using on desktop and laptop IMAP on both need help with folders Using Outlook 1
David McKay VBA to manually forward using odd options Outlook VBA and Custom Forms 1
H Stationery using between OL 2019 and OL 2010 Using Outlook 0
P Prevent Outlook 2016 from using DASL filter Using Outlook 4
O Calendar - Location: what happens when using my own way of entering locations Using Outlook 1
M Disable Contact Card Results when using "Search People" in Outlook Ribbon Using Outlook 7
K can't get custom form to update multiple contacts using VBA Outlook VBA and Custom Forms 3
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
pcunite Outlook 2019/O365 Build 13127.20408 errors when using MAPI calls Using Outlook 1
B Change Font and Font size using VBA Outlook VBA and Custom Forms 9
M Outlook 2013 reminder email by using Outlook vba Outlook VBA and Custom Forms 2
X Using Outlook 2013 and Outlook 365 Using Outlook 1
A Going to folder using shortcuts Using Outlook 3
A Outlook replies not using "delivered to" address in From Using Outlook 1
Terry Sullivan E-Mails Sent Using a Group Box Result in 70 Kickbacks Using Outlook 4
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
K Using Outlook 2016 to draw Using Outlook 1
O Outlook 365 - suddenly unable to send using Gmail POP3 Using Outlook 10
N Disable Auto Read Receipts sent after using Advanced Find Using Outlook 4
G Outlook 2016 sync contacts directly between phone and computer using outlook 2016 Using Outlook 0
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
O Save attachments using hotkey without changing attributes Outlook VBA and Custom Forms 1
J Add an Attachment Using an Array and Match first 17 Letters to Matching Template .oft to Send eMail Outlook VBA and Custom Forms 2
A Edit subject - and change conversationTopic - using VBA and redemption Outlook VBA and Custom Forms 2
A Using or not using apostrophes in search terms has this changed? Using Outlook 0
O Office 365 using POP3 on both laptop and desktop Using Outlook 0
M Using field names to capture a data element Using Outlook 0
B Vba to monitor time to respond to emails using a shared mailbox Outlook VBA and Custom Forms 5
B Looking to get the Recipient email address (or even the "friendly name") from an email I am replying to using VBA Outlook VBA and Custom Forms 4
D Using a VBA Custom Form to Send Reoccurring Email Upon Task Completion Outlook VBA and Custom Forms 4

Similar threads

Back
Top