Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Emailing in excel 2003
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 163
Default Emailing in excel 2003
Hi all Guru, I have created a button which emails the addresses as shown
below, what i would prefer if i could have it so that it emails what ever
email address is in cell B10, instead of having set email addresses.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

wbBook.Close True

DefaultFolder = "M:\Procurement\Approved PIPS"

If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFileName = "Project Brief" & " for " &
Sheets("PIP").Range("A13").Value



If Right(UCase(DefaultFileName), 3) < "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If

FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")

If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If

End If

If Response = vbYes Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _
Sheets("PIP").Range("b13").Value & " " & "has been accepted"

On Error Resume Next
With OutMail
.To = ; "
.CC = ""
.BCC = ""
.Subject = "PIP Accepted"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 561
Default Emailing in excel 2003
Check out Ron De Bruin tips:
http://www.rondebruin.nl/sendmail.htm
Micky


"Neil Holden" wrote:

Hi all Guru, I have created a button which emails the addresses as shown
below, what i would prefer if i could have it so that it emails what ever
email address is in cell B10, instead of having set email addresses.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

wbBook.Close True

DefaultFolder = "M:\Procurement\Approved PIPS"

If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFileName = "Project Brief" & " for " &
Sheets("PIP").Range("A13").Value



If Right(UCase(DefaultFileName), 3) < "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If

FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")

If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If

End If

If Response = vbYes Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _
Sheets("PIP").Range("b13").Value & " " & "has been accepted"

On Error Resume Next
With OutMail
.To = ; "
.CC = ""
.BCC = ""
.Subject = "PIP Accepted"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 163
Default Emailing in excel 2003
Thanks for the tip, however I have found the one i need on there but it
doesn't work correctly. I've been trying to do this for ages now and its
really annoying.

Please help me.

Neil.

"מיכאל (מיקי) אבידן" wrote:

Check out Ron De Bruin tips:
http://www.rondebruin.nl/sendmail.htm
Micky


"Neil Holden" wrote:

Hi all Guru, I have created a button which emails the addresses as shown
below, what i would prefer if i could have it so that it emails what ever
email address is in cell B10, instead of having set email addresses.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

wbBook.Close True

DefaultFolder = "M:\Procurement\Approved PIPS"

If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFileName = "Project Brief" & " for " &
Sheets("PIP").Range("A13").Value



If Right(UCase(DefaultFileName), 3) < "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If

FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")

If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If

End If

If Response = vbYes Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _
Sheets("PIP").Range("b13").Value & " " & "has been accepted"

On Error Resume Next
With OutMail
.To = ; "
.CC = ""
.BCC = ""
.Subject = "PIP Accepted"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 561
Default Emailing in excel 2003
It is easier to provide you with a new code that to "debug" yours.
What, EXACTLY, do you want to send by mail to the recipient who's address is
in B10 !
Micky


"Neil Holden" wrote:

Thanks for the tip, however I have found the one i need on there but it
doesn't work correctly. I've been trying to do this for ages now and its
really annoying.

Please help me.

Neil.

"מיכאל (מיקי) אבידן" wrote:

Check out Ron De Bruin tips:
http://www.rondebruin.nl/sendmail.htm
Micky


"Neil Holden" wrote:

Hi all Guru, I have created a button which emails the addresses as shown
below, what i would prefer if i could have it so that it emails what ever
email address is in cell B10, instead of having set email addresses.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

wbBook.Close True

DefaultFolder = "M:\Procurement\Approved PIPS"

If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFileName = "Project Brief" & " for " &
Sheets("PIP").Range("A13").Value



If Right(UCase(DefaultFileName), 3) < "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If

FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")

If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If

End If

If Response = vbYes Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _
Sheets("PIP").Range("b13").Value & " " & "has been accepted"

On Error Resume Next
With OutMail
.To = ; "
.CC = ""
.BCC = ""
.Subject = "PIP Accepted"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 163
Default Emailing in excel 2003
Hi Micky, the user will input an email address in B10, this will be different
on a weekly basis, when the button is pressed i want to email the worksheet
to the email address in B10.

Thanks.

"מיכאל (מיקי) אבידן" wrote:

It is easier to provide you with a new code that to "debug" yours.
What, EXACTLY, do you want to send by mail to the recipient who's address is
in B10 !
Micky


"Neil Holden" wrote:

Thanks for the tip, however I have found the one i need on there but it
doesn't work correctly. I've been trying to do this for ages now and its
really annoying.

Please help me.

Neil.

"מיכאל (מיקי) אבידן" wrote:

Check out Ron De Bruin tips:
http://www.rondebruin.nl/sendmail.htm
Micky


"Neil Holden" wrote:

Hi all Guru, I have created a button which emails the addresses as shown
below, what i would prefer if i could have it so that it emails what ever
email address is in cell B10, instead of having set email addresses.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

wbBook.Close True

DefaultFolder = "M:\Procurement\Approved PIPS"

If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFileName = "Project Brief" & " for " &
Sheets("PIP").Range("A13").Value



If Right(UCase(DefaultFileName), 3) < "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If

FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")

If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If

End If

If Response = vbYes Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _
Sheets("PIP").Range("b13").Value & " " & "has been accepted"

On Error Resume Next
With OutMail
.To = ; "
.CC = ""
.BCC = ""
.Subject = "PIP Accepted"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,123
Default Emailing in excel 2003
Hi Neal

Click on the Tip link that you find on each example page and you will find your answer

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Neil Holden" wrote in message ...
Hi Micky, the user will input an email address in B10, this will be different
on a weekly basis, when the button is pressed i want to email the worksheet
to the email address in B10.

Thanks.

"מיכאל (מיקי) אבידן" wrote:

It is easier to provide you with a new code that to "debug" yours.
What, EXACTLY, do you want to send by mail to the recipient who's address is
in B10 !
Micky


"Neil Holden" wrote:

Thanks for the tip, however I have found the one i need on there but it
doesn't work correctly. I've been trying to do this for ages now and its
really annoying.

Please help me.

Neil.

"מיכאל (מיקי) אבידן" wrote:

Check out Ron De Bruin tips:
http://www.rondebruin.nl/sendmail.htm
Micky


"Neil Holden" wrote:

Hi all Guru, I have created a button which emails the addresses as shown
below, what i would prefer if i could have it so that it emails what ever
email address is in cell B10, instead of having set email addresses.

Private Sub CommandButton1_Click()

ActiveWorkbook.Save

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Approve this PIP?", _
vbYesNo + vbInformation + vbDefaultButton2)

wbBook.Close True

DefaultFolder = "M:\Procurement\Approved PIPS"

If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFileName = "Project Brief" & " for " &
Sheets("PIP").Range("A13").Value



If Right(UCase(DefaultFileName), 3) < "XLS" Then
DefaultFileName = DefaultFileName & " " & _
Format(Date, "dd-mm-yyyy") & ".xls"
End If

FileToSave = Application.GetSaveAsFilename _
(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
& "*.xls", Title:="Save File As...")

If FileToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs _
Filename:=FileToSave, _
FileFormat:=ActiveWorkbook.FileFormat
End If

End If

If Response = vbYes Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _
Sheets("PIP").Range("b13").Value & " " & "has been accepted"

On Error Resume Next
With OutMail
.To = ; "
.CC = ""
.BCC = ""
.Subject = "PIP Accepted"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Emailing in excel 2003 Neil Holden Excel Discussion (Misc queries) 0 January 28th 10 04:20 PM
Emailing in excel 2003 Neil Holden Excel Discussion (Misc queries) 1 January 28th 10 11:53 AM
Emailing from within Excel Reggiee Excel Discussion (Misc queries) 0 October 1st 07 09:29 AM
Emailing Excel lsmft Excel Discussion (Misc queries) 4 March 17th 06 11:59 AM
How to emailing file from Excel 2000 to 2003, cannot open?? bennyw0528 Excel Discussion (Misc queries) 2 November 1st 05 10:37 PM


All times are GMT +1. The time now is 07:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement