Excel Template sending e-mails

Shockwave IIC

New Member
Joined
Jun 19, 2015
Messages
31
Morning

I have been tasked with "fixing" and excel template created by someone else who no longer works for the company.

We fill in the template, press a "clicky" then it saves and send the completed template to the client.


The issue is that the company images that appeared in the E-mail are now replaced with "Tinypic" sad emoji advising that the image is not found, suggesting to me that the previous employee hosted said images off site (Not sure why), I would of thought it would of made sense to have Excel open a saved E-mail Template and use that.

So I need to solve that (I have what I understand to be the VBA password, though my VBA experience is limited)

Go forward I also need to, put in a method for a user (Manager) to change the address the e-mail gets sent to, should the need arise.

So, I am hoping that I can create a E-mail template for each of the Clients Excel templates and get the Excel template to use that instead of whatever it is currently using, and thus the managers can open and change that email template when the e-mail address need updating.

Thanks in advance.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

From your description of the problem you are facing ...

you only need to make sure your target image location is correct ...
 

Shockwave IIC

New Member
Joined
Jun 19, 2015
Messages
31
Okay, spent 30 mins trying to work out how to get up the VBA code, only to work out that the clicky had been deleted, thus there is no VBA code anymore.

So i am now here - https://www.makeuseof.com/tag/send-emails-excel-vba/

Reading on how to do it.

Problems
  1. the strFrom field - needs to be the person clicking the button e-mail, not a fixed e-mail
  2. strBody - "The text I place in between these quotations" is fine (I think, can I just hit enter for a new line for the text?) but I'm needing the file to attached itself to the e-mail<code></code>
  3. SMTP??
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
If I were you I would rather to go to website showing how to do it with the following code:
Code:
Sub SubName()
Dim DistList As Variant
DistList = Array("name1","name2")
ActiveWorkbook.SendMail Recipients:=DistList, Subject:="subject"

End Sub

Not sure how it varies from the original code that I found, but so far it worked flawlessly.
 

Shockwave IIC

New Member
Joined
Jun 19, 2015
Messages
31
Okay, sorry more questions,

This is what I've got so far
Code:
Sub Template_to_Ecar()
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    TempFilePath = Environ$("X:\CONTROL\Folder name") & "\"
    [COLOR=#ffa500]TempFileName = Str(Sheet1.Cells(C7)) & "Text" & Str(Sheet1.Cells(C16))[/COLOR]
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
   On Error Resume Next
    With OutMail
        .To = "Removed for Data Protection"
        .CC = ""
        .BCC = ""
        .Subject = Str(Sheet1.Cells(C7)) & "Text" & Str(Sheet1.Cells(C16))
        .Body = ""
        Select Case Time
        Case Is < TimeValue("12:00")
            msg = "Good morning"
        Case Is < TimeValue("16:00")
            msg = "Good afternoon"
        Case Else
            msg = "Good evening"
    End Select
        .Body = "Please see attached completion details"
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
End Sub

Highlighted is the 1st (Of many no doubt) errors. I need the subject to be the same as file name, and I also think it will fail on the Body text as well.


EDIT to add
Will this just send a generica e-mail or will it send the users standard e-mail with signature?
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,775
Members
425,492
Latest member
blueexcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top