email address capture

[padadof2]

I have a sheet that lets users input email address as well as other information and then sends emails with attachments to all the address's on the list. Is there a way to store those email address's much like outlook does so when they start typing, it would autocomplete with email address's we have already input? Thank you in advance for any suggestions.

 

[padadof2]

is this possible?

 

[mark hansen]

So, the users access this spreadsheet more than once? First time to get their e-mail address in and the next time they can pull out the e-mail address?

 

[padadof2]

it would be multiple spreadsheets, but the more I look into this, the more I need to look at other avenues, as this wouldn't be the same spreadsheet, unless there is some way to save to the personal workbook?

 

[mark hansen]

If you are talking about saving to a personal space like in a user profile, could you save into a external data (or text) file. This could all the previously save e-mail address to be available. It may help to know more about the workflow. and the intended use.

 

[padadof2]

We are a construction company and we email proposals that have been created on excel. Each new project has a new workbook which is where we send the proposals from. Each project has a list of people we need to email the proposal to. So for every new project, we have someone enter the email addresss that we need to respond to. I have a macro set up, so we can highlight the list of emails and then it will send that email from outlook with the recipients as BCC. We end up sending emails to the same company/person, but with our set up, we have to re enter those emails each time. Hope this makes sense. I've attached the worksheet that we use to email the quotes. Thanks for looking into this. sample worksheet i use to email the quotes

 

[mark hansen]

If you need the same list of e-mails for each project (internal or external), you can have a back ground worksheet (I usually call mine "Configuration") where I hold various values that code references. I store all of my lists for drop down boxes etc.

In that background sheet, you read in a list of e-mail addresses that you may need to e-mail when the project workbook is opened. This list can be referenced to get your e-mails addresses without retyping them. You can get the e-mail address from that list in a variety of ways.

 

[padadof2]

Mark, that sounds perfect. Can you point me in a direction to get started? I'd enjoy learning how to do it

 

[mark hansen]

I use code I found at cPearson.com. (Exporting And Importing Text Into Excel) that has some good explanations that got me started. I made some modifications to allow me to make it some standard code I include in most excel projects. My code looks for the path and file name to load the file in my "Configuration" sheet so I can easily redirect it to the clients network space when I'm done with development and testing.

You will need to consider where to keep the text file (also known as a CSV file but I use .txt as the file extension so I can look at it with Notepad. I guess I'm old school). The text file should be in a network place that each user opening a Construction project file can get to. (I also have a routine to check if they have access and if not, I surface a message box, depending on the workflow).

I keep many excel projects in a read only location of SharePoint for distribution but they read the data from another folder. To make matters easier for the users, many lists have another spreadsheet to maintain the list so only key people can perform the updates. The excel file for saving/updating the list has the export routine from the website as well. I guess you don't need to do it that way; you can make any changes to the e-mail list in the project file, and export the e-mail list, incase there were changes as the file closes.

After the list in in the workbook, you can access the e-mail addresses in many ways.

 

[mark hansen]

Another consideration is if this excel file will be e-mail to someone without access to the text file. If this file is going to someone outside your company, you can use the code to check to see if they have read access to the folder where the e-mail address are kept... If not, don't import, of course, and keep going. I use the following to test if the user has read access to the correct place in the network.

Sub testwrite()
    Dim Con As Worksheet
    
    Set Con = Sheet10
    xPath = Con.Range("AN31").value
    
    If TestWriteAccess(xPath) Then
     ' You can Read and write to the folder
    Else
      ' You cannot Write
       MsgBox "You do not have access to the Data File.  Contact your FAIMS to gain access to the proper folders." & _
       vbCr & vbCr & "This template will close."
          
       Application.Quit
      
    End If
End Sub
Function FileExists(FilePath As String) As Boolean
Dim TestStr As String
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

If I need to test to see if the user has read/write access, I use this code:

Public Function TestWriteAccess(ByVal strpath As String) As Boolean
    Dim StrName As String, iFile As Integer, iCount As Integer, BExists As Boolean
   
    'Set the initial output to False
    TestWriteAccess = False
   
    'Ensure the file path has a trailing slash
    If Right(strpath, 1) <> "\" Then strpath = strpath & "\"
   
    'Ensure the path exists and is a folder
    On Error Resume Next
    BExists = (GetAttr(strpath) And vbDirectory) = vbDirectory
    If Not BExists Then GoTo Exit_TestWriteAccess 'Folder does not exist
    'Set error handling - return False if we encounter an error (folder does not exist or file cannot be created)
    On Error GoTo Exit_TestWriteAccess
   
    'Get the first available file name
    Do
        StrName = strpath & "TestWriteAccess" & iCount & ".tmp"
        iCount = iCount + 1
    Loop Until Dir(StrName) = vbNullString
   
    'Attempt to create a test file
    iFile = FreeFile()
   
    Open StrName For Output As #iFile
    Write #iFile, "Testing folder access"
    Close #iFile
   
    TestWriteAccess = True
   
    'Delete our test file
    Kill StrName
   
Exit_TestWriteAccess:
End Function

I think I got this code on this forum.