send mail through Outlook

[G2K]

Hi all,


I need to send a mail to 250 person. the content of mail will remain same for all concerns but i want to address each one of them individually. i have stored the individauls name in Column A and their mailing Address in column B. i want to create a macro who sends mail to all Individuals which name is maintioned in Columns A using mail address in respective column B.the excel sheet name is List.

any help would be deeply appriciated

thanks

 

[toe222]

I stumbled on your post looking for something and figured I would give it a try.

Sub Email()

Dim OutApp As Object
Dim OutMail As Object
Dim MyFile As String
Dim i As Integer

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

'MyFile = Application.GetOpenFilename
MyFile = ActiveWorkbook.FullName

With OutMail
i = 1
For Each cell In Range("a1:a250")
If cell <> "" Then
.Recipients.Add Range("b" & i)
End If
i = i + 1
Next cell
'.cc = "Carbon Copy here..."
.Subject = "Your Subject here..."
.HTMLBody = "Your Body here..."
.Attachments.Add MyFile
.Display
'.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

End Sub

 

[G2K]

thanks for Your Reply.

below is my code to send multiple mails. but i am facing a very annoying problem here. every time a pop up window comes up with a message " A Programme is trying to automatically send a message on Your behalf, Do you want to allow this? if it is unexpected, it may be a virus. and you should close no". i just want to bypass this message as you know i need to send multiple mails and i do not want to press click every time. it is also time consuming task.

i also want to attach signature in every e-mail. is there any way to insert signature dynamically???

thanks is advance

Private Sub Saveandsend()
Dim OutlookApp As Object
Dim MItem As Object
Dim Ws As Worksheet
Dim Str As String
'Create Excel sheet link
Set Ws = Sheets("List")
For I = 1 To Range(Range("A1"), Range("A1").End(xlDown)).Count

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = Ws.Range("B" & I).Value
.CC = ""
.Subject = Ws.Range("c1").Value
.body = "Dear " & Ws.Range("A" & I).Value & "," & vbCrLf & "" & vbCrLf & " " & Ws.Range("d1").Value & " " & vbNewLine & " " & Ws.Range("d2").Value & " " & vbNewLine & " " & Ws.Range("d3").Value & " " & vbNewLine & " " & vbNewLine & " " & Ws.Range("E1").Value

.OriginatorDeliveryReportRequested = True ' delivery confirmation
.ReadReceiptRequested = True ' read confirmation
.Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name
.display
'.send
End With
Next
End Sub

 

[toe222]

I get a similar message the first time i try to send an email with this code with a workbook, but once I click "allow" it does not pop back up. I am not sure what causes this.....you might could try lowering your securing setting for all macros. One other thing you can try is putting application.screenalerts = false and top of your sub and then true at the bottom. I am not sure if this will work but worth a shot.

As far a the signature I looked into this earlier in the week. It seems to be possible but not very easy so I did not pursue. If you want to search for .getinspector. And if you figure it out please post cause I would like to have the code. My workaround is to include my name, company, and number in the body.

 

[G2K]

Thanks for your reply,

Application.screenalerts = True/False is not applicable in this case. and this is not related to macro level security as well. it can be bypassed only by mail server

signature can be added by a API call in procedure or add it in mailbody using HTML codes. like

strhtml = '<FONT Face=Verdana FONT Size=2 Color=#151B8D>thanks and regards<br> '
strhtml = strhtml & ..........................
.htmlbody = strhtml

please post the code if you find any simple way to bypass security message ot add signature.

 

[GTO]

Greetings,

This security feature is not something you can bypass with a bit of code or changing settings. There is purchasable software called Outlook Redemption. I have never used, so no opinion.

If you do not need a password to logon to Outlook, or, if you don't mind hiding your password somewhere in the workbook or code, check out using CDO. Very good examples / explanations at Chip Pearson's site.

Mark